工作遇到sql写法，总结与学习（1）提醒及其设置，各并集和子查询集的链接查询

select top 20 a.日期,a.modelname,a.modelid,convert(varchar(10), isnull(b.条数,0))条数 from 
(
  select convert(varchar,getdate(),23)日期,* from Tb_Common_ModelInfo where messageid=17
  union all
  select convert(varchar, DATEADD(DAY,-1,getdate()),23),* from  Tb_Common_ModelInfo where messageid=17 ) a 
  left join
  (
     select a.日期,a.RemindTypeID 提醒类型,a.条数 from 
     (
           select convert(varchar,reminddate,23)日期,
           count(*) 条数,
           '0'IsRead ,
           remindtypeid
      from WFT_RemindRecord a where 1=1 
      and convert(varchar,reminddate,23)>=convert(varchar, DATEADD(DAY,-180,getdate()),23) and IsRead='0' 
      group by remindtypeid,reminddate
) a ) b on a.日期=b.日期 and a.modelid=b.提醒类型  order by 日期 desc,a.OrderID

USE [jj360]
GO

/****** Object:  Table [dbo].[Tb_Common_ModelInfo]    Script Date: 2019/10/21 10:48:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Tb_Common_ModelInfo](
	[ModelID] [varchar](38) NOT NULL,
	[ModelName] [varchar](200) NULL,
	[ParentID] [int] NULL,
	[OrderID] [int] NULL,
	[MessageID] [int] NULL,
 CONSTRAINT [PK_Tb_Common_ModelInfo] PRIMARY KEY CLUSTERED 
(
	[ModelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER FUNCTION dbo.splitl (
    @String VARCHAR(MAX),
    @Delimiter VARCHAR(MAX)
) RETURNS @temptable TABLE (items VARCHAR(MAX)) AS
BEGIN
    DECLARE @idx INT=1
    DECLARE @slice VARCHAR(MAX) 
    IF LEN(@String) < 1 OR LEN(ISNULL(@String,'')) = 0
        RETURN
    WHILE @idx != 0
    BEGIN
        SET @idx = CHARINDEX(@Delimiter,@String)

        IF @idx != 0
            SET @slice = LEFT(@String,@idx - 1)
        ELSE
            SET @slice = @String

        IF LEN(@slice) > 0
            INSERT INTO @temptable(items) VALUES(@slice)

        SET @String = RIGHT (@String, LEN(@String) - @idx)

        IF LEN(@String) = 0
            BREAK
    END
    RETURN
END