SQL怎么查出这样的数据？
declare @table table([id] int ,[name] varchar(10),price numeric(12,2),beginDate datetime,endDate datetime)declare @tableDetail table([id] int,[fid] int,price numeric(12,2),beginDate datetime,endDate datetime)insert into @table(id,name,price,beginDate,endDate)select 1,'张三',0.00,'2012-07-01','2012-08-31'insert into @tableDetail(id,fid,price,beginDate,endDate)select 1,1,10.00,'2012-07-05','2012-07-10' union allselect 2,1,10.00,'2012-07-08','2012-07-20' union allselect 3,1,10.00,'2012-07-25','2012-07-31' union allselect 4,1,10.00,'2012-08-01','2012-08-20' union allselect 5,1,10.00,'2012-08-08','2012-08-15' 想查到下面的这样的数据，从7月1号到8月31号每一天都有价格就算是0也有数据出来。/*name price beginDate endDate---------- ----------- ------------- ----------张三 0.00 2012-07-01 2012-07-04 张三 10.00 2012-07-05 2012-07-07 张三 20.00 2012-07-08 2012-07-20 张三 0.00 2012-07-21 2012-07-24 张三 10.00 2012-07-25 2012-07-31 张三 10.00 2012-08-01 2012-08-07 张三 20.00 2012-08-08 2012-08-15 张三 10.00 2012-08-16 2012-08-20张三 0.00 2012-08-21 2012-08-31*/
declare @table table([id] int ,[name] varchar(10),price numeric(12,2),beginDate datetime,endDate datetime)declare @tableDetail table([id] int,[fid] int,price numeric(12,2),beginDate datetime,endDate datetime)insert into @table(id,name,price,beginDate,endDate)select 1,'张三',0.00,'2012-07-01','2012-08-31' UNIONselect 2,'李四',0.00,'2012-07-01','2012-09-30' insert into @tableDetail(id,fid,price,beginDate,endDate)select 1,1,10.00,'2012-07-01','2012-07-10' union allselect 2,1,10.00,'2012-07-02','2012-07-20' union allselect 3,1,10.00,'2012-07-02','2012-07-20' union allselect 4,1,10.00,'2012-07-20','2012-07-31' union allselect 5,1,10.00,'2012-08-01','2012-08-20' UNION allselect 6,2,10.00,'2012-07-05','2012-07-10' union allselect 7,2,10.00,'2012-07-08','2012-07-20' union allselect 8,2,10.00,'2012-07-25','2012-07-31' union allselect 9,2,10.00,'2012-08-01','2012-08-20' union allselect 10,2,10.00,'2012-08-08','2012-08-15';WITH t0 AS( SELECT fid,SUM(price) AS price,beginDate,endDate FROM @tableDetail GROUP BY fid,beginDate,endDate),t1 AS(SELECT DISTINCT A.name,A.BeginDate AS AllDateFROM @table AS A INNER JOIN t0 AS B ON A.id = B.fidUNION ALLSELECT DISTINCT A.name,B.BeginDateFROM @table AS A INNER JOIN t0 AS B ON A.id = B.fidUNION ALLSELECT A.name,B.EndDateFROM @table AS A INNER JOIN t0 AS B ON A.id = B.fidUNION ALL(SELECT A.name,A.endDateFROM @table AS A INNER JOIN t0 AS B ON A.id = B.fidUNION SELECT A.name,DATEADD(DAY,-1,B.BeginDate)FROM @table AS A INNER JOIN t0 AS B ON A.id = B.fid AND NOT EXISTS (SELECT 1 FROM @table AS C INNER JOIN t0 AS D ON C.id = D.fid AND DATEADD(DAY,-1,B.BeginDate) = D.endDate) AND DATEADD(DAY,-1,B.BeginDate) > A.beginDate)UNION ALLSELECT A.name,DATEADD(DAY,1,B.EndDate)FROM @table AS A INNER JOIN t0 AS B ON A.id = B.fid AND NOT EXISTS (SELECT 1 FROM @table AS C INNER JOIN t0 AS D ON C.id = D.fid AND DATEADD(DAY,1,B.endDate) = D.beginDate) AND DATEADD(DAY,1,B.endDate) < A.endDate),t2 AS( SELECT name,AllDate,RN=ROW_NUMBER() OVER (PARTITION BY name ORDER BY AllDate) FROM t1),t3 AS(SELECT A.name,A.AllDate AS BeginDate,B.AllDate AS EndDateFROM t2 AS A,t2 AS BWHERE A.RN = B.RN - 1 AND A.RN % 2 = 1 AND B.RN % 2 = 0 AND A.name = B.name)SELECT t3.Name, ISNULL(SUM(A.price),0) AS Price, CONVERT(VARCHAR(10),t3.beginDate,20) AS beginDate, CONVERT(VARCHAR(10),t3.endDate,20) AS endDateFROM t0 AS A INNER JOIN @table AS B ON A.fid = B.id RIGHT OUTER JOIN t3 ON A.beginDate <= t3.BeginDate AND t3.EndDate <= A.endDate AND B.name = t3.nameGROUP BY t3.Name,t3.beginDate,t3.endDateORDER BY t3.Name,BeginDate,EndDateName Price beginDate endDate李四 0.00 2012-07-01 2012-07-04李四 10.00 2012-07-05 2012-07-07李四 20.00 2012-07-08 2012-07-10李四 10.00 2012-07-11 2012-07-20李四 0.00 2012-07-21 2012-07-24李四 10.00 2012-07-25 2012-07-31李四 10.00 2012-08-01 2012-08-07李四 20.00 2012-08-08 2012-08-15李四 10.00 2012-08-16 2012-08-20李四 0.00 2012-08-21 2012-09-30张三 10.00 2012-07-01 2012-07-01张三 30.00 2012-07-02 2012-07-10张三 20.00 2012-07-11 2012-07-19张三 30.00 2012-07-20 2012-07-20张三 10.00 2012-07-21 2012-07-31张三 10.00 2012-08-01 2012-08-20张三 0.00 2012-08-21 2012-08-31
[解决办法]
declare @table table([id] int ,[name] varchar(10),price numeric(12,2),beginDate datetime,endDate datetime)declare @tableDetail table([id] int,[fid] int,price numeric(12,2),beginDate datetime,endDate datetime)insert into @table(id,name,price,beginDate,endDate)select 1,'张三',0.00,'2012-07-01','2012-08-31' insert into @tableDetail(id,fid,price,beginDate,endDate)select 1,1,10.00,'2012-07-01','2012-07-10' union allselect 2,1,10.00,'2012-07-02','2012-07-20' union allselect 3,1,10.00,'2012-07-02','2012-07-20' union allselect 4,1,10.00,'2012-07-20','2012-07-31' union allselect 5,1,10.00,'2012-08-01','2012-08-20' union allselect 6,1,10.00,'2012-08-15','2012-09-20' SELECT T.*INTO #List FROM( SELECT T.fid [ID],T.beginDate AS [date],'B' AS flag FROM @tableDetail T WHERE T.beginDate>=(SELECT T2.beginDate FROM @table T2 WHERE T2.id=T.fid) UNION ALL SELECT T.fid,T.endDate AS [date],'E' AS flag FROM @tableDetail T WHERE T.endDate<=(SELECT T2.endDate FROM @table T2 WHERE T2.id=T.fid) UNION ALL SELECT T.id,T.beginDate,'B' FROM @table T UNION all SELECT T.id,T.endDate,'E' FROM @table T ) TGROUP BY T.[date],T.flag,T.IDORDER BY T.[date]SELECT M.name, T.[BeginDate], T.[EndDate], ISNULL(SUM(L.Price),0) AS PriceFROM( SELECT DISTINCT CONVERT(VARCHAR(10),CASE WHEN List.flag='E' THEN DATEADD(DD,1,List.[date]) ELSE List.[date] END,120) AS [BeginDate], CONVERT(VARCHAR(10),CASE WHEN (SELECT COUNT(1) FROM #List T WHERE T.[date]=List.[date] AND T.ID=List.ID)>1 AND List.flag='B' THEN List.[date] ELSE CASE WHEN (SELECT MIN(T.[date]) FROM #List T WHERE T.[date]>List.date AND T.ID=List.ID)=(SELECT MAX(T.[date]) FROM #List T WHERE T.ID=List.ID) THEN (SELECT MIN(T.[date]) FROM #List T WHERE T.[date]>List.date AND T.ID=List.ID) ELSE DATEADD ( DD ,CASE WHEN (SELECT TOP 1 T.flag FROM #List T WHERE T.[date]>List.[date] AND T.ID=List.ID ORDER BY T.[date],T.flag)='E' THEN 0 ELSE -1 END ,(SELECT MIN(T.[date]) FROM #List T WHERE T.[date]>List.[date] AND T.ID=List.ID) ) END END,120) AS [EndDate] ,List.ID FROM #List List) TLEFT JOIN @tableDetail L ON L.beginDate<=T.EndDate and L.endDate>=T.BeginDate AND L.fid = T.IDRIGHT JOIN @table M ON M.id = T.IDWHERE T.[BeginDate]<=T.[EndDate]GROUP BY M.name,T.[BeginDate],T.[EndDate]ORDER BY M.name,T.[BeginDate],T.[EndDate]DROP TABLE #List/*name BeginDate EndDate Price---------- ---------- ---------- -----------张三 2012-07-01 2012-07-01 10.00张三 2012-07-02 2012-07-10 30.00张三 2012-07-11 2012-07-19 20.00张三 2012-07-20 2012-07-20 30.00张三 2012-07-21 2012-07-31 10.00张三 2012-08-01 2012-08-14 10.00张三 2012-08-15 2012-08-20 20.00张三 2012-08-21 2012-08-31 10.00(8 行受影响)*/
