问个关于SQL查询语句，大家帮帮嘛，谢谢了！！
我有个表，表中有如下字段
CREATE TABLE [dbo].[tbName](
[dwdm] [varchar](10) NULL,--单位代码
[dwmc] [varchar](60) NULL,--单位名称
[qk] [numeric](19,2) NULL,--累计欠款金额
[qkts] [int] NULL,--累计欠款天数
[djsj] [datetime] NOT NULL--登记时间
)
我想按照 qkts 统计下，大概效果如下
单位代码 单位名称 0-30天 30-60天 60-90天... 330-360天 360天以上 合计
31-AC 单位2 2019.58 -99.90 -99.90 ... 0.00 0.00 1819.78
33-AC 单位1 2019.58 -99.90 -99.90 ... 0.00 0.00 1819.78
就是取对应的qkts在0-30内djsj最大所对应的qk，qkts在30-60内djsj最大所对应的qk，qkts在60-90内djsj最大所对应的qk....
测试数据如下
insert into tbName
select '31-AC','单位2', 1535.26,34,'2010-03-01 00:01:46.217' union all
select '33-AC','单位1',-99.90,42,'2010-03-01 00:01:46.217' union all
select '33-AC','单位1',-99.90,43,'2010-03-02 00:01:46.420' union all
select '31-AC','单位2',1535.26,35,'2010-03-02 00:01:46.420' union all
select '33-AC','单位1',1535.26,36,'2010-03-03 00:01:47.077' union all
select '33-AC','单位1',-99.90,44,'2010-03-03 00:01:47.077' union all
select '33-AC','单位1',-99.90,45,'2010-03-04 00:02:01.857' union all
select '33-AC','单位1',1535.26,37,'2010-03-04 00:02:01.857' union all
select '31-AC','单位2',1535.26,38,'2010-03-05 00:01:50.263' union all
select '33-AC','单位1',-99.90,46,'2010-03-05 00:01:50.263' union all
select '33-AC','单位1',-99.90,47,'2010-03-06 00:02:57.450' union all
select '31-AC','单位2',1535.26,39,'2010-03-06 00:02:57.450' union all
select '33-AC','单位1',1535.26,40,'2010-03-07 00:01:49.623' union all
select '33-AC','单位1',-99.90,48,'2010-03-07 00:01:49.623' union all
select '33-AC','单位1',1535.26,41,'2010-03-08 00:01:48.107' union all
select '31-AC','单位2',-99.90,49,'2010-03-08 00:01:48.107' union all
select '33-AC','单位1',1535.26,42,'2010-03-09 00:01:49.247' union all
select '33-AC','单位1',-99.90,50,'2010-03-09 00:01:49.247' union all
select '33-AC','单位1',-99.90,51,'2010-03-10 00:01:48.590' union all
select '31-AC','单位2',1535.26,43,'2010-03-10 00:01:48.590' union all
select '33-AC','单位1',1535.26,44,'2010-03-11 00:01:48.530' union all
select '33-AC','单位1',-99.90,52,'2010-03-11 00:01:48.530' union all
select '33-AC','单位1',-99.90,53,'2010-03-12 00:01:49.233' union all
select '33-AC','单位1',1535.26,45,'2010-03-12 00:01:49.233' union all
select '31-AC','单位2',1535.26,46,'2010-03-13 00:01:48.437' union all
select '33-AC','单位1',-99.90,54,'2010-03-13 00:01:48.437' union all
select '33-AC','单位1',1535.26,47,'2010-03-14 00:01:46.670' union all
select '31-AC','单位2',-99.90,55,'2010-03-14 00:01:46.670' union all
select '33-AC','单位1',-99.90,56,'2010-03-15 00:01:46.530' union all
select '33-AC','单位1',1535.26,48,'2010-03-15 00:01:46.530' union all
select '33-AC','单位1',-99.90,57,'2010-03-16 00:01:47.060' union all
select '33-AC','单位1',1535.26,49,'2010-03-16 00:01:47.060' union all
select '33-AC','单位1',-99.90,58,'2010-03-17 00:01:48.530' union all
select '33-AC','单位1',1535.26,50,'2010-03-17 00:01:48.530' union all
select '33-AC','单位1',1535.26,51,'2010-03-18 00:01:49.623' union all
select '33-AC','单位1',-99.90,59,'2010-03-18 00:01:49.623' union all
select '31-AC','单位2',-99.90,60,'2010-03-19 00:02:49.513' union all
select '33-AC','单位1',1535.26,52,'2010-03-19 00:02:49.513' union all
select '33-AC','单位1',1535.26,53,'2010-03-20 00:01:47.857' union all
select '33-AC','单位1',-99.90,61,'2010-03-20 00:01:47.857' union all
select '33-AC','单位1',1535.26,54,'2010-03-21 00:01:47.390' union all
select '33-AC','单位1',-99.90,62,'2010-03-21 00:01:47.390' union all
select '33-AC','单位1',1535.26,55,'2010-03-22 00:01:47.717' union all
select '33-AC','单位1',-99.90,63,'2010-03-22 00:01:47.717' union all
select '33-AC','单位1',2019.58,11,'2010-03-23 00:05:32.030' union all
select '33-AC','单位1',2019.58,12,'2010-03-24 00:01:48.000' union all
select '33-AC','单位1',2019.58,13,'2010-03-25 00:01:47.810' union all
select '33-AC','单位1',2019.58,14,'2010-03-26 00:01:48.577' union all
select '31-AC','单位2',2019.58,15,'2010-03-27 00:01:47.357' union all
select '33-AC','单位1',2019.58,16,'2010-03-28 00:01:49.247' union all
select '31-AC','单位2',2019.58,17,'2010-03-29 00:01:50.357' union all
select '33-AC','单位1',2019.58,18,'2010-03-30 00:01:47.107' union all
select '33-AC','单位1',2019.58,19,'2010-03-31 00:01:47.467'
大家帮帮忙，辛苦了！！
[解决办法]
CREATE TABLE [dbo].[tbName](
[dwdm] [varchar](10) NULL,--单位代码
[dwmc] [varchar](60) NULL,--单位名称
[qk] [numeric](19,2) NULL,--累计欠款金额
[qkts] [int] NULL,--累计欠款天数
[djsj] [datetime] NOT NULL--登记时间
)
insert into tbName
select '31-AC','单位2', 1535.26,34,'2010-03-01 00:01:46.217' union all
select '33-AC','单位1',-99.90,42,'2010-03-01 00:01:46.217' union all
select '33-AC','单位1',-99.90,43,'2010-03-02 00:01:46.420' union all
select '31-AC','单位2',1535.26,35,'2010-03-02 00:01:46.420' union all
select '33-AC','单位1',1535.26,36,'2010-03-03 00:01:47.077' union all
select '33-AC','单位1',-99.90,44,'2010-03-03 00:01:47.077' union all
select '33-AC','单位1',-99.90,45,'2010-03-04 00:02:01.857' union all
select '33-AC','单位1',1535.26,37,'2010-03-04 00:02:01.857' union all
select '31-AC','单位2',1535.26,38,'2010-03-05 00:01:50.263' union all
select '33-AC','单位1',-99.90,46,'2010-03-05 00:01:50.263' union all
select '33-AC','单位1',-99.90,47,'2010-03-06 00:02:57.450' union all
select '31-AC','单位2',1535.26,39,'2010-03-06 00:02:57.450' union all
select '33-AC','单位1',1535.26,40,'2010-03-07 00:01:49.623' union all
select '33-AC','单位1',-99.90,48,'2010-03-07 00:01:49.623' union all
select '33-AC','单位1',1535.26,41,'2010-03-08 00:01:48.107' union all
select '31-AC','单位2',-99.90,49,'2010-03-08 00:01:48.107' union all
select '33-AC','单位1',1535.26,42,'2010-03-09 00:01:49.247' union all
select '33-AC','单位1',-99.90,50,'2010-03-09 00:01:49.247' union all
select '33-AC','单位1',-99.90,51,'2010-03-10 00:01:48.590' union all
select '31-AC','单位2',1535.26,43,'2010-03-10 00:01:48.590' union all
select '33-AC','单位1',1535.26,44,'2010-03-11 00:01:48.530' union all
select '33-AC','单位1',-99.90,52,'2010-03-11 00:01:48.530' union all
select '33-AC','单位1',-99.90,53,'2010-03-12 00:01:49.233' union all
select '33-AC','单位1',1535.26,45,'2010-03-12 00:01:49.233' union all
select '31-AC','单位2',1535.26,46,'2010-03-13 00:01:48.437' union all
select '33-AC','单位1',-99.90,54,'2010-03-13 00:01:48.437' union all
select '33-AC','单位1',1535.26,47,'2010-03-14 00:01:46.670' union all
select '31-AC','单位2',-99.90,55,'2010-03-14 00:01:46.670' union all
select '33-AC','单位1',-99.90,56,'2010-03-15 00:01:46.530' union all
select '33-AC','单位1',1535.26,48,'2010-03-15 00:01:46.530' union all
select '33-AC','单位1',-99.90,57,'2010-03-16 00:01:47.060' union all
select '33-AC','单位1',1535.26,49,'2010-03-16 00:01:47.060' union all
select '33-AC','单位1',-99.90,58,'2010-03-17 00:01:48.530' union all
select '33-AC','单位1',1535.26,50,'2010-03-17 00:01:48.530' union all
select '33-AC','单位1',1535.26,51,'2010-03-18 00:01:49.623' union all
select '33-AC','单位1',-99.90,59,'2010-03-18 00:01:49.623' union all
select '31-AC','单位2',-99.90,60,'2010-03-19 00:02:49.513' union all
select '33-AC','单位1',1535.26,52,'2010-03-19 00:02:49.513' union all
select '33-AC','单位1',1535.26,53,'2010-03-20 00:01:47.857' union all
select '33-AC','单位1',-99.90,61,'2010-03-20 00:01:47.857' union all
select '33-AC','单位1',1535.26,54,'2010-03-21 00:01:47.390' union all
select '33-AC','单位1',-99.90,62,'2010-03-21 00:01:47.390' union all
select '33-AC','单位1',1535.26,55,'2010-03-22 00:01:47.717' union all
select '33-AC','单位1',-99.90,63,'2010-03-22 00:01:47.717' union all
select '33-AC','单位1',2019.58,11,'2010-03-23 00:05:32.030' union all
select '33-AC','单位1',2019.58,12,'2010-03-24 00:01:48.000' union all
select '33-AC','单位1',2019.58,13,'2010-03-25 00:01:47.810' union all
select '33-AC','单位1',2019.58,14,'2010-03-26 00:01:48.577' union all
select '31-AC','单位2',2019.58,15,'2010-03-27 00:01:47.357' union all
select '33-AC','单位1',2019.58,16,'2010-03-28 00:01:49.247' union all
select '31-AC','单位2',2019.58,17,'2010-03-29 00:01:50.357' union all
select '33-AC','单位1',2019.58,18,'2010-03-30 00:01:47.107' union all
select '33-AC','单位1',2019.58,19,'2010-03-31 00:01:47.467'
select
[dwdm],
[dwmc],
(select top 1 [qk] from [tbName] where [qkts] between 0 and 30 order by djsj desc) as [0-30天],
(select top 1 [qk] from [tbName] where [qkts] between 31 and 60 order by djsj desc) as [31-60天],
(select top 1 [qk] from [tbName] where [qkts] between 61 and 90 order by djsj desc) as [61-90天],
(select top 1 [qk] from [tbName] where [qkts] between 91 and 120 order by djsj desc) as [91-120天],
(select top 1 [qk] from [tbName] where [qkts] between 121 and 150 order by djsj desc) as [121-150天],
(select top 1 [qk] from [tbName] where [qkts] between 151 and 180 order by djsj desc) as [151-180天],
(select top 1 [qk] from [tbName] where [qkts] between 181 and 210 order by djsj desc) as [181-210天],
(select top 1 [qk] from [tbName] where [qkts] between 211 and 240 order by djsj desc) as [211-240天],
(select top 1 [qk] from [tbName] where [qkts] between 241 and 270 order by djsj desc) as [241-270天],
(select top 1 [qk] from [tbName] where [qkts] between 271 and 300 order by djsj desc) as [271-300天],
(select top 1 [qk] from [tbName] where [qkts] between 301 and 330 order by djsj desc) as [301-330天],
(select top 1 [qk] from [tbName] where [qkts] between 331 and 360 order by djsj desc) as [331-360天],
(select top 1 [qk] from [tbName] where [qkts] >=361 order by djsj desc) as [360天以上]
from
[tbName]
group by
[dwdm],
[dwmc]
/*
dwdmdwmc0-30天31-60天61-90天91-120天121-150天151-180天181-210天211-240天241-270天271-300天301-330天331-360天360天以上
31-AC单位22019.581535.26-99.90NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
33-AC单位12019.581535.26-99.90NULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
*/
[解决办法]
要是有负数就要用null而不能用0了，刚才用你最后给的数据测了，看满足你兽欲没
SELECT dwdm,dwmc,MAX([0-30]) [0-30],MAX([31-60])[31-60]
FROM (
SELECT dwdm,dwmc, MAX(qk) AS [0-30] ,null [31-60]
FROM tbname a
WHERE EXISTS (SELECT 1 FROM (SELECT dwdm, MAX(qkts)qkts FROM tbname WHERE qkts BETWEEN 0 AND 30 GROUP BY dwdm) b
WHERE a.qkts=b.qkts AND a.dwdm=b.dwdm )
GROUP BY dwdm,dwmc
UNION ALL
SELECT dwdm,dwmc, null [0-30] ,MAX(qk) AS [31-60]
FROM tbname a
WHERE EXISTS (SELECT 1 FROM (SELECT dwdm,MAX(qkts)qkts FROM tbname WHERE qkts BETWEEN 31 AND 60 GROUP BY dwdm) b
WHERE a.qkts=b.qkts AND a.dwdm=b.dwdm )
GROUP BY dwdm,dwmc
)a
GROUP BY dwdm,dwmc
[解决办法]
這樣OK?
USE test
GO
IF OBJECT_ID('tbName')IS NOT NULL
DROP TABLE [tbName]
CREATE TABLE [dbo].[tbName](
[dwdm] [nvarchar](10) NULL, --单位代码
[dwmc] [nvarchar](60) NULL, --单位名称
[qk] [numeric](19,2) NULL, --累计欠款金额
[qkts] [int] NULL, --累计欠款天数
[djsj] [datetime] NOT NULL --登记时间
)
GO
insert into tbName
select N'31-AC',N'单位2', 1535.26, 34, '2010-03-01 00:01:46.217' union all
select N'33-AC',N'单位1', -99.90, 42, '2010-03-01 00:01:46.217' union all
select N'33-AC',N'单位1', -99.90, 43, '2010-03-02 00:01:46.420' union all
select N'31-AC',N'单位2', 1535.26, 35, '2010-03-02 00:01:46.420' union all
select N'33-AC',N'单位1', 1535.26, 36, '2010-03-03 00:01:47.077' union all
select N'33-AC',N'单位1', -99.90, 44, '2010-03-03 00:01:47.077' union all
select N'33-AC',N'单位1', -99.90, 45, '2010-03-04 00:02:01.857' union all
select N'33-AC',N'单位1', 1535.26, 37, '2010-03-04 00:02:01.857' union all
select N'31-AC',N'单位2', 1535.26, 38, '2010-03-05 00:01:50.263' union all
select N'33-AC',N'单位1', -99.90, 46, '2010-03-05 00:01:50.263' union all
select N'33-AC',N'单位1', -99.90, 47, '2010-03-06 00:02:57.450' union all
select N'31-AC',N'单位2', 1535.26, 39, '2010-03-06 00:02:57.450' union all
select N'33-AC',N'单位1', 1535.26, 40, '2010-03-07 00:01:49.623' union all
select N'33-AC',N'单位1', -99.90, 48, '2010-03-07 00:01:49.623' union all
select N'33-AC',N'单位1', 1535.26, 41, '2010-03-08 00:01:48.107' union all
select N'31-AC',N'单位2', -99.90, 49, '2010-03-08 00:01:48.107' union all
select N'33-AC',N'单位1', 1535.26, 42, '2010-03-09 00:01:49.247' union all
select N'33-AC',N'单位1', -99.90, 50, '2010-03-09 00:01:49.247' union all
select N'33-AC',N'单位1', -99.90, 51, '2010-03-10 00:01:48.590' union all
select N'31-AC',N'单位2', 1535.26, 43, '2010-03-10 00:01:48.590' union all
select N'33-AC',N'单位1', 1535.26, 44, '2010-03-11 00:01:48.530' union all
select N'33-AC',N'单位1', -99.90, 52, '2010-03-11 00:01:48.530' union all
select N'33-AC',N'单位1', -99.90, 53, '2010-03-12 00:01:49.233' union all
select N'33-AC',N'单位1', 1535.26, 45, '2010-03-12 00:01:49.233' union all
select N'31-AC',N'单位2', 1535.26, 46, '2010-03-13 00:01:48.437' union all
select N'33-AC',N'单位1', -99.90, 54, '2010-03-13 00:01:48.437' union all
select N'33-AC',N'单位1', 1535.26, 47, '2010-03-14 00:01:46.670' union all
select N'31-AC',N'单位2', -99.90, 55, '2010-03-14 00:01:46.670' union all
select N'33-AC',N'单位1', -99.90, 56, '2010-03-15 00:01:46.530' union all
select N'33-AC',N'单位1', 1535.26, 48, '2010-03-15 00:01:46.530' union all
select N'33-AC',N'单位1', -99.90, 57, '2010-03-16 00:01:47.060' union all
select N'33-AC',N'单位1', 1535.26, 49, '2010-03-16 00:01:47.060' union all
select N'33-AC',N'单位1', -99.90, 58, '2010-03-17 00:01:48.530' union all
select N'33-AC',N'单位1', 1535.26, 50, '2010-03-17 00:01:48.530' union all
select N'33-AC',N'单位1', 1535.26, 51, '2010-03-18 00:01:49.623' union all
select N'33-AC',N'单位1', -99.90, 59, '2010-03-18 00:01:49.623' union all
select N'31-AC',N'单位2', -99.90, 60, '2010-03-19 00:02:49.513' union all
select N'33-AC',N'单位1', 1535.26, 52, '2010-03-19 00:02:49.513' union all
select N'33-AC',N'单位1', 1535.26, 53, '2010-03-20 00:01:47.857' union all
select N'33-AC',N'单位1', -99.90, 61, '2010-03-20 00:01:47.857' union all
select N'33-AC',N'单位1', 1535.26, 54, '2010-03-21 00:01:47.390' union all
select N'33-AC',N'单位1', -99.90, 62, '2010-03-21 00:01:47.390' union all
select N'33-AC',N'单位1', 1535.26, 55, '2010-03-22 00:01:47.717' union all
select N'33-AC',N'单位1', -99.90, 63, '2010-03-22 00:01:47.717' union all
select N'33-AC',N'单位1', 2019.58, 11, '2010-03-23 00:05:32.030' union all
select N'33-AC',N'单位1', 2019.58, 12, '2010-03-24 00:01:48.000' union all
select N'33-AC',N'单位1', 2019.58, 13, '2010-03-25 00:01:47.810' union all
select N'33-AC',N'单位1', 2019.58, 14, '2010-03-26 00:01:48.577' union all
select N'31-AC',N'单位2', 2019.58, 15, '2010-03-27 00:01:47.357' union all
select N'33-AC',N'单位1', 2019.58, 16, '2010-03-28 00:01:49.247' union all
select N'31-AC',N'单位2', 2019.58, 17, '2010-03-29 00:01:50.357' union all
select N'33-AC',N'单位1', 2019.58, 18, '2010-03-30 00:01:47.107' union all
select N'33-AC',N'单位1', 2019.58, 19, '2010-03-31 00:01:47.467'
GO
IF OBJECT_ID('tempdb..#Result')IS NOT NULL
DROP TABLE #Result
SELECT
*
,CASE
WHEN qkts>=0 AND qkts<=30
THEN '0-30天'
WHEN qkts>30 AND qkts<=60
THEN '30-60天'
WHEN qkts>60 AND qkts<=90
THEN '60-90天'
WHEN qkts>90 AND qkts<=120
THEN '90-120天'
WHEN qkts>120 AND qkts<=150
THEN '120-150天'
WHEN qkts>150 AND qkts<=180
THEN '150-180天'
WHEN qkts>180 AND qkts<=210
THEN '180-210天'
WHEN qkts>210 AND qkts<=240
THEN '210-240天'
WHEN qkts>240 AND qkts<=270
THEN '240-270天'
WHEN qkts>270 AND qkts<=300
THEN '270-300天'
WHEN qkts>300 AND qkts<=330
THEN '300-330天'
WHEN qkts>330 AND qkts<=360
THEN '330-360天'
ELSE '360天以上'
END AS type
INTO #Result
FROM tbName
---- Result :
SELECT
dwdm
,dwmc
,SUM(CASE WHEN type='0-30天' THEN qk ELSE 0 END) AS [0-30天]
,SUM(CASE WHEN type='30-60天' THEN qk ELSE 0 END) AS [30-60天]
,SUM(CASE WHEN type='60-90天' THEN qk ELSE 0 END) AS [60-90天]
,SUM(CASE WHEN type='90-120天' THEN qk ELSE 0 END) AS [90-120天]
,SUM(CASE WHEN type='120-150天' THEN qk ELSE 0 END) AS [120-150天]
,SUM(CASE WHEN type='150-180天' THEN qk ELSE 0 END) AS [150-180天]
,SUM(CASE WHEN type='180-210天' THEN qk ELSE 0 END) AS [180-210天]
,SUM(CASE WHEN type='210-240天' THEN qk ELSE 0 END) AS [210-240天]
,SUM(CASE WHEN type='240-270天' THEN qk ELSE 0 END) AS [240-270天]
,SUM(CASE WHEN type='270-300天' THEN qk ELSE 0 END) AS [270-300天]
,SUM(CASE WHEN type='300-330天' THEN qk ELSE 0 END) AS [300-330天]
,SUM(CASE WHEN type='330-360天' THEN qk ELSE 0 END) AS [330-360天]
,SUM(CASE WHEN type='360天以上' THEN qk ELSE 0 END) AS [360天以上]
,SUM(qk) AS 合计
FROM #Result AS a
WHERE NOT EXISTS(SELECT 1 FROM #Result AS x
WHERE x.type=a.type
AND x.dwdm=a.dwdm
AND x.dwmc=a.dwmc
AND x.djsj>a.djsj-- 最大djsj
)
AND NOT EXISTS(SELECT 1 FROM #Result AS x
WHERE x.type=a.type
AND x.dwdm=a.dwdm
AND x.dwmc=a.dwmc
AND x.djsj=a.djsj-- 相同djsj時,最大qkts
AND x.qkts>a.qkts
)
GROUP BY dwdm,dwmc
/*
dwdm dwmc 0-30天 30-60天 60-90天 90-120天 120-150天 150-180天 180-210天 210-240天 240-270天 270-300天 300-330天 330-360天 360天以上 合计
------ ------ -------- -------- -------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
33-AC 单位1 2019.58 1535.26 -99.90 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3454.94
31-AC 单位2 2019.58 -99.90 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1919.68
*/
[解决办法]
我也贴个，这样应该可以了
CREATE TABLE [dbo].[#tbName](
[dwdm] [varchar](10) NULL, --单位代码
[dwmc] [varchar](60) NULL, --单位名称
[qk] [numeric](19,2) NULL, --累计欠款金额
[qkts] [int] NULL, --累计欠款天数
[djsj] [datetime] NOT NULL --登记时间
)
insert into #tbName
select '31-AC','单位2', 1535.26, 34, '2010-03-01 00:01:46.217' union all
select '33-AC','单位1', -99.90, 42, '2010-03-01 00:01:46.217' union all
select '33-AC','单位1', -99.90, 43, '2010-03-02 00:01:46.420' union all
select '31-AC','单位2', 1535.26, 35, '2010-03-02 00:01:46.420' union all
select '33-AC','单位1', 1535.26, 36, '2010-03-03 00:01:47.077' union all
select '33-AC','单位1', -99.90, 44, '2010-03-03 00:01:47.077' union all
select '33-AC','单位1', -99.90, 45, '2010-03-04 00:02:01.857' union all
select '33-AC','单位1', 1535.26, 37, '2010-03-04 00:02:01.857' union all
select '31-AC','单位2', 1535.26, 38, '2010-03-05 00:01:50.263' union all
select '33-AC','单位1', -99.90, 46, '2010-03-05 00:01:50.263' union all
select '33-AC','单位1', -99.90, 47, '2010-03-06 00:02:57.450' union all
select '31-AC','单位2', 1535.26, 39, '2010-03-06 00:02:57.450' union all
select '33-AC','单位1', 1535.26, 40, '2010-03-07 00:01:49.623' union all
select '33-AC','单位1', -99.90, 48, '2010-03-07 00:01:49.623' union all
select '33-AC','单位1', 1535.26, 41, '2010-03-08 00:01:48.107' union all
select '31-AC','单位2', -99.90, 49, '2010-03-08 00:01:48.107' union all
select '33-AC','单位1', 1535.26, 42, '2010-03-09 00:01:49.247' union all
select '33-AC','单位1', -99.90, 50, '2010-03-09 00:01:49.247' union all
select '33-AC','单位1', -99.90, 51, '2010-03-10 00:01:48.590' union all
select '31-AC','单位2', 1535.26, 43, '2010-03-10 00:01:48.590' union all
select '33-AC','单位1', 1535.26, 44, '2010-03-11 00:01:48.530' union all
select '33-AC','单位1', -99.90, 52, '2010-03-11 00:01:48.530' union all
select '33-AC','单位1', -99.90, 53, '2010-03-12 00:01:49.233' union all
select '33-AC','单位1', 1535.26, 45, '2010-03-12 00:01:49.233' union all
select '31-AC','单位2', 1535.26, 46, '2010-03-13 00:01:48.437' union all
select '33-AC','单位1', -99.90, 54, '2010-03-13 00:01:48.437' union all
select '33-AC','单位1', 1535.26, 47, '2010-03-14 00:01:46.670' union all
select '31-AC','单位2', -99.90, 55, '2010-03-14 00:01:46.670' union all
select '33-AC','单位1', -99.90, 56, '2010-03-15 00:01:46.530' union all
select '33-AC','单位1', 1535.26, 48, '2010-03-15 00:01:46.530' union all
select '33-AC','单位1', -99.90, 57, '2010-03-16 00:01:47.060' union all
select '33-AC','单位1', 1535.26, 49, '2010-03-16 00:01:47.060' union all
select '33-AC','单位1', -99.90, 58, '2010-03-17 00:01:48.530' union all
select '33-AC','单位1', 1535.26, 50, '2010-03-17 00:01:48.530' union all
select '33-AC','单位1', 1535.26, 51, '2010-03-18 00:01:49.623' union all
select '33-AC','单位1', -99.90, 59, '2010-03-18 00:01:49.623' union all
select '31-AC','单位2', -99.90, 60, '2010-03-19 00:02:49.513' union all
select '33-AC','单位1', 1535.26, 52, '2010-03-19 00:02:49.513' union all
select '33-AC','单位1', 1535.26, 53, '2010-03-20 00:01:47.857' union all
select '33-AC','单位1', -99.90, 61, '2010-03-20 00:01:47.857' union all
select '33-AC','单位1', 1535.26, 54, '2010-03-21 00:01:47.390' union all
select '33-AC','单位1', -99.90, 62, '2010-03-21 00:01:47.390' union all
select '33-AC','单位1', 1535.26, 55, '2010-03-22 00:01:47.717' union all
select '33-AC','单位1', -99.90, 63, '2010-03-22 00:01:47.717' union all
select '33-AC','单位1', 2019.58, 11, '2010-03-23 00:05:32.030' union all
select '33-AC','单位1', 2019.58, 12, '2010-03-24 00:01:48.000' union all
select '33-AC','单位1', 2019.58, 13, '2010-03-25 00:01:47.810' union all
select '33-AC','单位1', 2019.58, 14, '2010-03-26 00:01:48.577' union all
select '31-AC','单位2', 2019.58, 15, '2010-03-27 00:01:47.357' union all
select '33-AC','单位1', 2019.58, 16, '2010-03-28 00:01:49.247' union all
select '31-AC','单位2', 2019.58, 17, '2010-03-29 00:01:50.357' union all
select '33-AC','单位1', 2019.58, 18, '2010-03-30 00:01:47.107' union all
select '33-AC','单位1', 2019.58, 19, '2010-03-31 00:01:47.467'
SELECT dwdm,
dwmc,
(SELECT TOP 1 qk FROM #tbName a WHERE a.dwdm=b.dwdm AND qkts BETWEEN 0 AND 30 ORDER BY djsj desc) AS [0-30天],
(SELECT TOP 1 qk FROM #tbName a WHERE a.dwdm=b.dwdm AND qkts BETWEEN 31 AND 60 ORDER BY djsj desc) AS [30-60天],
(SELECT TOP 1 qk FROM #tbName a WHERE a.dwdm=b.dwdm AND qkts BETWEEN 61 AND 90 ORDER BY djsj desc) AS [60-90天]
FROM #tbName b GROUP BY dwdm,dwmc
--results
dwdmdwmc0-30天30-60天60-90天
31-AC单位22019.58-99.90NULL
33-AC单位12019.581535.26-99.90