求一个sql逻辑的实现

SRCS000 2012-12-17 02:00:26
请问情况如下


CREATE TABLE A
(
A1 varchar(2),
A2 DATETIME
)


DECLARE @TEMP1 VARCHAR(1)
DECLARE @TEMPAT DATETIME
DECLARE @TEMPR INT
DECLARE @TEMP2 INT
SELECT @TEMPR=6
WHILE @TEMPR>0
BEGIN
SELECT @TEMP2=20
WHILE @TEMP2>0
BEGIN
SELECT @TEMPAT=DATEadd(day,@TEMPR,getdate())
SELECT @TEMP1 = CONVERT(varchar(6), (cast(ceiling(rand() * 9) as int)))
INSERT INTO A VALUES( @TEMP1,@TEMPAT)
SELECT @TEMP2=@TEMP2-1
END

SELECT @TEMPR=@TEMPR-1

END


如果获取 A表中 在A2天数中 A1的数量>2 并且只显示每天数量在前2名的一个排列？

比如

A1 当天出现次数 A2
1 8 2012-12-14
1 6 2012-12-15
2 4 2012-12-15
4 6 2012-12-15

如果有用一条sql语句实现就是理想了。请各位大神教教小弟吧。谢谢

...全文
298 37 打赏 收藏 转发到动态 举报
写回复
用AI写文章
37 条回复
切换为时间正序
请发表友善的回复…
发表回复
黄_瓜 2012-12-18
  • 打赏
  • 举报
 回复
引用 35 楼 SRCS000 的回复:
引用 33 楼 Beirut 的回复:引用 26 楼 SRCS000 的回复:简单点说例如下列数据 SQL code?123456789101112/*A1 出现次数 时间1 13 201212192 13 201212193 14 201212191 8 201212202 14 20121220……
看到晚了，问题很简单，就是你描述的有问题，一开始就像26楼那么说早就可以解决了
SRCS000 2012-12-18
  • 打赏
  • 举报
 回复
引用 33 楼 Beirut 的回复:
引用 26 楼 SRCS000 的回复:简单点说例如下列数据 SQL code?123456789101112/*A1 出现次数 时间1 13 201212192 13 201212193 14 201212191 8 201212202 14 201212203 18 20121220……
呵呵斑竹你慢了。
黄_瓜 2012-12-18
  • 打赏
  • 举报
 回复
晕。。。搞定了啊
黄_瓜 2012-12-18
  • 打赏
  • 举报
 回复
引用 26 楼 SRCS000 的回复:
简单点说例如下列数据 SQL code?123456789101112/*A1 出现次数 时间1 13 201212192 13 201212193 14 201212191 8 201212202 14 201212203 18 201212201 13 201212212……

--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO 
CREATE TABLE #tb([A1] INT,[出现次数] INT,[时间] DATETIME)
INSERT #tb
SELECT 1,13,'20121219' UNION ALL
SELECT 2,13,'20121219' UNION ALL
SELECT 3,14,'20121219' UNION ALL
SELECT 1,8,'20121220' UNION ALL
SELECT 2,14,'20121220' UNION ALL
SELECT 3,18,'20121220' UNION ALL
SELECT 1,13,'20121221' UNION ALL
SELECT 2,13,'20121221' UNION ALL
SELECT 3,14,'20121221'
--------------开始查询--------------------------
--2005以及以上版本
SELECT [A1],[出现次数],[时间] FROM 
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY [时间] ORDER BY [出现次数] DESC ) AS row_id FROM #tb
) t 
WHERE row_id<3
ORDER BY [时间]
----------------结果----------------------------
/* 
A1	出现次数	时间
3	14	2012-12-19 00:00:00.000
1	13	2012-12-19 00:00:00.000
3	18	2012-12-20 00:00:00.000
2	14	2012-12-20 00:00:00.000
3	14	2012-12-21 00:00:00.000
1	13	2012-12-21 00:00:00.000
*/
SRCS000 2012-12-18
  • 打赏
  • 举报
 回复
请问怎么给分啊？
SRCS000 2012-12-18
  • 打赏
  • 举报
 回复
引用 30 楼 qianjin036a 的回复:
排序上再加一个. select A1,dt,ct from( select row_number()over(partition by dt order by ct desc,A1) as rn,* from( SELECT A1 ,COUNT(A1)ct, CONVERT(VARCHAR(8),A2,112)dt FROM A GROUP BY A1 ,CONVE……
给力 主要 row_number()over(partition by dt order by ct desc,A1) 这里学习了 ，谢谢~~~
-晴天 2012-12-18
  • 打赏
  • 举报
 回复
排序上再加一个. select A1,dt,ct from( select row_number()over(partition by dt order by ct desc,A1) as rn,* from( SELECT A1 ,COUNT(A1)ct, CONVERT(VARCHAR(8),A2,112)dt FROM A GROUP BY A1 ,CONVERT(VARCHAR(8),A2,112) HAVING COUNT(A1)>2 )T)T2 WHERE rn<=2
-晴天 2012-12-18
  • 打赏
  • 举报
 回复
你要的是这个意思吗? A1 A2 ---- ----------------------- 6 2012-12-24 11:09:48.013 8 2012-12-24 11:09:48.013 6 2012-12-24 11:09:48.013 9 2012-12-24 11:09:48.013 1 2012-12-24 11:09:48.030 1 2012-12-24 11:09:48.030 2 2012-12-24 11:09:48.030 4 2012-12-24 11:09:48.030 9 2012-12-24 11:09:48.030 6 2012-12-24 11:09:48.030 3 2012-12-24 11:09:48.030 4 2012-12-24 11:09:48.030 6 2012-12-24 11:09:48.030 8 2012-12-24 11:09:48.030 7 2012-12-24 11:09:48.030 7 2012-12-24 11:09:48.030 1 2012-12-24 11:09:48.030 8 2012-12-24 11:09:48.030 6 2012-12-24 11:09:48.030 6 2012-12-24 11:09:48.030 6 2012-12-23 11:09:48.030 3 2012-12-23 11:09:48.030 7 2012-12-23 11:09:48.030 5 2012-12-23 11:09:48.030 3 2012-12-23 11:09:48.030 9 2012-12-23 11:09:48.030 6 2012-12-23 11:09:48.030 3 2012-12-23 11:09:48.030 7 2012-12-23 11:09:48.030 3 2012-12-23 11:09:48.030 8 2012-12-23 11:09:48.030 7 2012-12-23 11:09:48.030 8 2012-12-23 11:09:48.030 5 2012-12-23 11:09:48.030 2 2012-12-23 11:09:48.030 5 2012-12-23 11:09:48.030 2 2012-12-23 11:09:48.030 7 2012-12-23 11:09:48.030 8 2012-12-23 11:09:48.030 3 2012-12-23 11:09:48.030 5 2012-12-22 11:09:48.030 8 2012-12-22 11:09:48.030 1 2012-12-22 11:09:48.030 8 2012-12-22 11:09:48.030 1 2012-12-22 11:09:48.030 9 2012-12-22 11:09:48.030 4 2012-12-22 11:09:48.030 7 2012-12-22 11:09:48.030 1 2012-12-22 11:09:48.030 3 2012-12-22 11:09:48.030 1 2012-12-22 11:09:48.030 4 2012-12-22 11:09:48.030 2 2012-12-22 11:09:48.030 7 2012-12-22 11:09:48.030 3 2012-12-22 11:09:48.030 8 2012-12-22 11:09:48.030 9 2012-12-22 11:09:48.030 7 2012-12-22 11:09:48.043 4 2012-12-22 11:09:48.043 9 2012-12-22 11:09:48.043 9 2012-12-21 11:09:48.043 3 2012-12-21 11:09:48.043 4 2012-12-21 11:09:48.043 8 2012-12-21 11:09:48.043 4 2012-12-21 11:09:48.043 4 2012-12-21 11:09:48.043 8 2012-12-21 11:09:48.043 1 2012-12-21 11:09:48.043 2 2012-12-21 11:09:48.043 6 2012-12-21 11:09:48.043 3 2012-12-21 11:09:48.043 1 2012-12-21 11:09:48.043 5 2012-12-21 11:09:48.043 5 2012-12-21 11:09:48.043 8 2012-12-21 11:09:48.043 3 2012-12-21 11:09:48.043 8 2012-12-21 11:09:48.043 6 2012-12-21 11:09:48.043 4 2012-12-21 11:09:48.043 9 2012-12-21 11:09:48.043 2 2012-12-20 11:09:48.043 6 2012-12-20 11:09:48.043 7 2012-12-20 11:09:48.043 1 2012-12-20 11:09:48.043 9 2012-12-20 11:09:48.043 8 2012-12-20 11:09:48.043 8 2012-12-20 11:09:48.043 1 2012-12-20 11:09:48.043 3 2012-12-20 11:09:48.043 8 2012-12-20 11:09:48.043 9 2012-12-20 11:09:48.043 9 2012-12-20 11:09:48.043 8 2012-12-20 11:09:48.043 9 2012-12-20 11:09:48.043 8 2012-12-20 11:09:48.043 6 2012-12-20 11:09:48.043 6 2012-12-20 11:09:48.043 4 2012-12-20 11:09:48.043 6 2012-12-20 11:09:48.043 5 2012-12-20 11:09:48.043 1 2012-12-19 11:09:48.043 5 2012-12-19 11:09:48.043 3 2012-12-19 11:09:48.043 9 2012-12-19 11:09:48.043 4 2012-12-19 11:09:48.043 8 2012-12-19 11:09:48.043 4 2012-12-19 11:09:48.043 5 2012-12-19 11:09:48.043 7 2012-12-19 11:09:48.043 6 2012-12-19 11:09:48.060 9 2012-12-19 11:09:48.060 5 2012-12-19 11:09:48.060 9 2012-12-19 11:09:48.060 3 2012-12-19 11:09:48.060 7 2012-12-19 11:09:48.060 8 2012-12-19 11:09:48.060 6 2012-12-19 11:09:48.060 6 2012-12-19 11:09:48.060 1 2012-12-19 11:09:48.060 4 2012-12-19 11:09:48.060 (120 行受影响) select A1,dt,ct from( select row_number()over(partition by dt order by ct desc) as rn,* from( SELECT A1 ,COUNT(A1)ct, CONVERT(VARCHAR(8),A2,112)dt FROM A GROUP BY A1 ,CONVERT(VARCHAR(8),A2,112) HAVING COUNT(A1)>2 )T)T2 WHERE rn<=2 /* A1 dt ct ---- -------- ----------- 4 20121219 3 5 20121219 3 8 20121220 5 9 20121220 4 4 20121221 4 8 20121221 4 1 20121222 4 4 20121222 3 3 20121223 5 7 20121223 4 6 20121224 6 8 20121224 3 (12 行受影响) */
我腫了 2012-12-18
  • 打赏
  • 举报
 回复
引用 27 楼 SRCS000 的回复:
引用 25 楼 bbbbbben 的回复:這個OK了. SQL code?12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879……
如果次數相同,只要A1靠前的?
SRCS000 2012-12-18
  • 打赏
  • 举报
 回复
引用 25 楼 bbbbbben 的回复:
這個OK了. SQL code?12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899……
就是每天只显示 次数最多的前2个A1的值对应的 一个集合。
SRCS000 2012-12-18
  • 打赏
  • 举报
 回复
简单点说例如下列数据 

/*
A1      出现次数  时间
1	13	20121219
2	13	20121219
3	14	20121219
1	8	20121220
2	14	20121220
3	18	20121220
1	13	20121221
2	13	20121221
3	14	20121221
*/
再处理后得到 最终结果 A1 出现次数 时间 3 14 20121219 1 13 20121219 3 18 20121220 2 14 20121220 3 14 20121221 1 13 20121221
我腫了 2012-12-18
  • 打赏
  • 举报
 回复
這個OK了. 
USE test
GO

---->生成表tb
--IF object_id('A')IS NOT NULL
--    DROP TABLE A
--CREATE TABLE A
--(
--A1 varchar(2),   
--A2 DATETIME
--)
--
--
--DECLARE @TEMP1 VARCHAR(1)
--DECLARE @TEMPAT DATETIME
--DECLARE @TEMPR INT
--DECLARE @TEMP2 INT
--SELECT @TEMPR=6
--WHILE @TEMPR>0
--BEGIN
--SELECT @TEMP2=20
--WHILE @TEMP2>0
--BEGIN
--SELECT @TEMPAT=DATEadd(day,@TEMPR,getdate())
--SELECT @TEMP1 = CONVERT(varchar(6), (cast(ceiling(rand() * 9) as int)))
--INSERT INTO A VALUES( @TEMP1,@TEMPAT)
--SELECT @TEMP2=@TEMP2-1
--END
--
--SELECT @TEMPR=@TEMPR-1
--
--END
-- 
------------------------------------------ data -------

/*
 
A1    A2
--- -------------------------
4	2012-12-24 10:40:59.553
8	2012-12-24 10:40:59.560
9	2012-12-24 10:40:59.560
9	2012-12-24 10:40:59.560
2	2012-12-24 10:40:59.560
3	2012-12-24 10:40:59.560
1	2012-12-24 10:40:59.560
6	2012-12-24 10:40:59.560
3	2012-12-24 10:40:59.560
4	2012-12-24 10:40:59.560
8	2012-12-24 10:40:59.560
4	2012-12-24 10:40:59.560
7	2012-12-24 10:40:59.560
2	2012-12-24 10:40:59.560
1	2012-12-24 10:40:59.563
5	2012-12-24 10:40:59.563
6	2012-12-24 10:40:59.563
5	2012-12-24 10:40:59.563
8	2012-12-24 10:40:59.563
4	2012-12-24 10:40:59.563
6	2012-12-23 10:40:59.563
9	2012-12-23 10:40:59.563
5	2012-12-23 10:40:59.563
9	2012-12-23 10:40:59.563
6	2012-12-23 10:40:59.563
2	2012-12-23 10:40:59.563
5	2012-12-23 10:40:59.563
5	2012-12-23 10:40:59.563
3	2012-12-23 10:40:59.563
6	2012-12-23 10:40:59.567
2	2012-12-23 10:40:59.567
2	2012-12-23 10:40:59.567
3	2012-12-23 10:40:59.567
1	2012-12-23 10:40:59.567
2	2012-12-23 10:40:59.567
5	2012-12-23 10:40:59.567
7	2012-12-23 10:40:59.567
2	2012-12-23 10:40:59.567
9	2012-12-23 10:40:59.567
9	2012-12-23 10:40:59.567
4	2012-12-22 10:40:59.567
9	2012-12-22 10:40:59.567
1	2012-12-22 10:40:59.567
1	2012-12-22 10:40:59.567
1	2012-12-22 10:40:59.567
4	2012-12-22 10:40:59.570
2	2012-12-22 10:40:59.570
4	2012-12-22 10:40:59.570
3	2012-12-22 10:40:59.570
2	2012-12-22 10:40:59.570
9	2012-12-22 10:40:59.570
5	2012-12-22 10:40:59.570
9	2012-12-22 10:40:59.570
8	2012-12-22 10:40:59.570
6	2012-12-22 10:40:59.570
5	2012-12-22 10:40:59.570
8	2012-12-22 10:40:59.570
5	2012-12-22 10:40:59.570
6	2012-12-22 10:40:59.570
5	2012-12-22 10:40:59.570
1	2012-12-21 10:40:59.570
2	2012-12-21 10:40:59.570
8	2012-12-21 10:40:59.570
7	2012-12-21 10:40:59.570
2	2012-12-21 10:40:59.573
5	2012-12-21 10:40:59.573
5	2012-12-21 10:40:59.573
7	2012-12-21 10:40:59.573
9	2012-12-21 10:40:59.573
6	2012-12-21 10:40:59.573
5	2012-12-21 10:40:59.573
6	2012-12-21 10:40:59.573
6	2012-12-21 10:40:59.573
1	2012-12-21 10:40:59.573
4	2012-12-21 10:40:59.577
7	2012-12-21 10:40:59.577
6	2012-12-21 10:40:59.577
3	2012-12-21 10:40:59.577
6	2012-12-21 10:40:59.577
5	2012-12-21 10:40:59.577
3	2012-12-20 10:40:59.577
7	2012-12-20 10:40:59.577
4	2012-12-20 10:40:59.577
1	2012-12-20 10:40:59.577
7	2012-12-20 10:40:59.580
5	2012-12-20 10:40:59.580
3	2012-12-20 10:40:59.580
1	2012-12-20 10:40:59.580
8	2012-12-20 10:40:59.580
4	2012-12-20 10:40:59.580
3	2012-12-20 10:40:59.580
9	2012-12-20 10:40:59.580
5	2012-12-20 10:40:59.580
7	2012-12-20 10:40:59.580
1	2012-12-20 10:40:59.580
6	2012-12-20 10:40:59.580
3	2012-12-20 10:40:59.580
5	2012-12-20 10:40:59.580
3	2012-12-20 10:40:59.580
6	2012-12-20 10:40:59.580
2	2012-12-19 10:40:59.580
1	2012-12-19 10:40:59.580
2	2012-12-19 10:40:59.580
8	2012-12-19 10:40:59.580
6	2012-12-19 10:40:59.583
4	2012-12-19 10:40:59.583
3	2012-12-19 10:40:59.583
3	2012-12-19 10:40:59.583
1	2012-12-19 10:40:59.583
8	2012-12-19 10:40:59.583
7	2012-12-19 10:40:59.583
7	2012-12-19 10:40:59.583
9	2012-12-19 10:40:59.583
9	2012-12-19 10:40:59.583
3	2012-12-19 10:40:59.583
5	2012-12-19 10:40:59.583
6	2012-12-19 10:40:59.583
6	2012-12-19 10:40:59.583
4	2012-12-19 10:40:59.583
3	2012-12-19 10:40:59.587
 
*/
------------------------------------------- select ----


SELECT 
        * 
    FROM (
            SELECT                
                    A1    
                    ,COUNT(1) AS 当天出现次数 
                    ,CONVERT(VARCHAR(10),A2,120) AS A2
                FROM A
                GROUP BY CONVERT(VARCHAR(10),A2,120),A1
                HAVING COUNT(1)>2
    ) AS a
    WHERE EXISTS(SELECT 
                            1 
                        FROM (
                                SELECT 
										A1    
										,COUNT(1) AS 当天出现次数 
										,CONVERT(VARCHAR(10),A2,120) AS A2
                                    FROM A
                                    GROUP BY CONVERT(VARCHAR(10),A2,120),A1
                                    HAVING COUNT(1)>2
                        ) AS x
                        WHERE x.A2=a.A2
                            AND x.当天出现次数>a.当天出现次数
                        HAVING COUNT(1)<2
                )
    ORDER BY a.A2,a.当天出现次数 DESC
 
 
/*
A1  当天出现次数    A2
--- --------------- ------------
3    4				2012-12-19
6    3				2012-12-19
3    5				2012-12-20
5    3				2012-12-20
1    3				2012-12-20
7    3				2012-12-20
6    5				2012-12-21
5    4				2012-12-21
5    4				2012-12-22
4    3				2012-12-22
1    3				2012-12-22
9    3				2012-12-22
2    5				2012-12-23
9    4				2012-12-23
5    4				2012-12-23
4    4				2012-12-24
8    3				2012-12-24
*/

Go
SRCS000 2012-12-18
  • 打赏
  • 举报
 回复
引用 23 楼 bbbbbben 的回复:
應該是這樣吧? SQL code?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889……
数据还是有问题，主要是结果漏了数据，看看20号。 并且 HAVING COUNT(1) 这里修改 为 >0 和给出的例子结果居然一样。非常奇怪。不过感谢你的思路。谢谢~
我腫了 2012-12-18
  • 打赏
  • 举报
 回复
應該是這樣吧? 
USE test
GO

---->生成表tb
--IF object_id('A')IS NOT NULL
--    DROP TABLE A
--CREATE TABLE A
--(
--A1 varchar(2),   
--A2 DATETIME
--)
--
--
--DECLARE @TEMP1 VARCHAR(1)
--DECLARE @TEMPAT DATETIME
--DECLARE @TEMPR INT
--DECLARE @TEMP2 INT
--SELECT @TEMPR=6
--WHILE @TEMPR>0
--BEGIN
--SELECT @TEMP2=20
--WHILE @TEMP2>0
--BEGIN
--SELECT @TEMPAT=DATEadd(day,@TEMPR,getdate())
--SELECT @TEMP1 = CONVERT(varchar(6), (cast(ceiling(rand() * 9) as int)))
--INSERT INTO A VALUES( @TEMP1,@TEMPAT)
--SELECT @TEMP2=@TEMP2-1
--END
--
--SELECT @TEMPR=@TEMPR-1
--
--END
-- 
------------------------------------------ data -------

/*
 
A1    A2
--- -------------------------
4	2012-12-24 10:40:59.553
8	2012-12-24 10:40:59.560
9	2012-12-24 10:40:59.560
9	2012-12-24 10:40:59.560
2	2012-12-24 10:40:59.560
3	2012-12-24 10:40:59.560
1	2012-12-24 10:40:59.560
6	2012-12-24 10:40:59.560
3	2012-12-24 10:40:59.560
4	2012-12-24 10:40:59.560
8	2012-12-24 10:40:59.560
4	2012-12-24 10:40:59.560
7	2012-12-24 10:40:59.560
2	2012-12-24 10:40:59.560
1	2012-12-24 10:40:59.563
5	2012-12-24 10:40:59.563
6	2012-12-24 10:40:59.563
5	2012-12-24 10:40:59.563
8	2012-12-24 10:40:59.563
4	2012-12-24 10:40:59.563
6	2012-12-23 10:40:59.563
9	2012-12-23 10:40:59.563
5	2012-12-23 10:40:59.563
9	2012-12-23 10:40:59.563
6	2012-12-23 10:40:59.563
2	2012-12-23 10:40:59.563
5	2012-12-23 10:40:59.563
5	2012-12-23 10:40:59.563
3	2012-12-23 10:40:59.563
6	2012-12-23 10:40:59.567
2	2012-12-23 10:40:59.567
2	2012-12-23 10:40:59.567
3	2012-12-23 10:40:59.567
1	2012-12-23 10:40:59.567
2	2012-12-23 10:40:59.567
5	2012-12-23 10:40:59.567
7	2012-12-23 10:40:59.567
2	2012-12-23 10:40:59.567
9	2012-12-23 10:40:59.567
9	2012-12-23 10:40:59.567
4	2012-12-22 10:40:59.567
9	2012-12-22 10:40:59.567
1	2012-12-22 10:40:59.567
1	2012-12-22 10:40:59.567
1	2012-12-22 10:40:59.567
4	2012-12-22 10:40:59.570
2	2012-12-22 10:40:59.570
4	2012-12-22 10:40:59.570
3	2012-12-22 10:40:59.570
2	2012-12-22 10:40:59.570
9	2012-12-22 10:40:59.570
5	2012-12-22 10:40:59.570
9	2012-12-22 10:40:59.570
8	2012-12-22 10:40:59.570
6	2012-12-22 10:40:59.570
5	2012-12-22 10:40:59.570
8	2012-12-22 10:40:59.570
5	2012-12-22 10:40:59.570
6	2012-12-22 10:40:59.570
5	2012-12-22 10:40:59.570
1	2012-12-21 10:40:59.570
2	2012-12-21 10:40:59.570
8	2012-12-21 10:40:59.570
7	2012-12-21 10:40:59.570
2	2012-12-21 10:40:59.573
5	2012-12-21 10:40:59.573
5	2012-12-21 10:40:59.573
7	2012-12-21 10:40:59.573
9	2012-12-21 10:40:59.573
6	2012-12-21 10:40:59.573
5	2012-12-21 10:40:59.573
6	2012-12-21 10:40:59.573
6	2012-12-21 10:40:59.573
1	2012-12-21 10:40:59.573
4	2012-12-21 10:40:59.577
7	2012-12-21 10:40:59.577
6	2012-12-21 10:40:59.577
3	2012-12-21 10:40:59.577
6	2012-12-21 10:40:59.577
5	2012-12-21 10:40:59.577
3	2012-12-20 10:40:59.577
7	2012-12-20 10:40:59.577
4	2012-12-20 10:40:59.577
1	2012-12-20 10:40:59.577
7	2012-12-20 10:40:59.580
5	2012-12-20 10:40:59.580
3	2012-12-20 10:40:59.580
1	2012-12-20 10:40:59.580
8	2012-12-20 10:40:59.580
4	2012-12-20 10:40:59.580
3	2012-12-20 10:40:59.580
9	2012-12-20 10:40:59.580
5	2012-12-20 10:40:59.580
7	2012-12-20 10:40:59.580
1	2012-12-20 10:40:59.580
6	2012-12-20 10:40:59.580
3	2012-12-20 10:40:59.580
5	2012-12-20 10:40:59.580
3	2012-12-20 10:40:59.580
6	2012-12-20 10:40:59.580
2	2012-12-19 10:40:59.580
1	2012-12-19 10:40:59.580
2	2012-12-19 10:40:59.580
8	2012-12-19 10:40:59.580
6	2012-12-19 10:40:59.583
4	2012-12-19 10:40:59.583
3	2012-12-19 10:40:59.583
3	2012-12-19 10:40:59.583
1	2012-12-19 10:40:59.583
8	2012-12-19 10:40:59.583
7	2012-12-19 10:40:59.583
7	2012-12-19 10:40:59.583
9	2012-12-19 10:40:59.583
9	2012-12-19 10:40:59.583
3	2012-12-19 10:40:59.583
5	2012-12-19 10:40:59.583
6	2012-12-19 10:40:59.583
6	2012-12-19 10:40:59.583
4	2012-12-19 10:40:59.583
3	2012-12-19 10:40:59.587
 
*/
------------------------------------------- select ----
 
SELECT 
        * 
    FROM (
            SELECT                
                    A1    
                    ,COUNT(1) AS 当天出现次数 
                    ,CONVERT(VARCHAR(10),A2,120) AS A2
                FROM A
                GROUP BY CONVERT(VARCHAR(10),A2,120),A1
                HAVING COUNT(1)>2
    ) AS a
    WHERE EXISTS(SELECT 
                            1 
                        FROM (
                                SELECT 
                                        CONVERT(VARCHAR(10),A2,120) AS A2
                                        ,A1
                                        ,COUNT(1) AS 当天出现次数 
                                    FROM A
                                    GROUP BY CONVERT(VARCHAR(10),A2,120),A1
                                    HAVING COUNT(1)>2
                        ) AS x
                        WHERE x.A2=a.A2
                            AND x.当天出现次数>=a.当天出现次数
                        HAVING COUNT(1) IN (1,2)
                )
    ORDER BY a.A2,a.当天出现次数 DESC
 
 
/*
A1  当天出现次数    A2
--- --------------- ------------
3	4				2012-12-19
6	3				2012-12-19
3	5				2012-12-20
6	5				2012-12-21
5	4				2012-12-21
5	4				2012-12-22
2	5				2012-12-23
4	4				2012-12-24
8	3				2012-12-24
*/
 
Go
我腫了 2012-12-18
  • 打赏
  • 举报
 回复
引用 20 楼 SRCS000 的回复:
引用 16 楼 bbbbbben 的回复:SQL code?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283……
能不能拿個數據,然後給個結果集? 你就說結果不對,我也不知道是哪部份不對.
SRCS000 2012-12-18
  • 打赏
  • 举报
 回复
引用 19 楼 qianjin036a 的回复:
乱套了! select A1,dt,ct from( select row_number()over(partition by a1,dt order by ct desc) as rn,* from( SELECT A1 ,COUNT(A1)ct, CONVERT(VARCHAR(8),A2,112)dt FROM A GROUP BY A1 ,CONVERT(V……
转了一个圈后。好像没有什么区别啊。不过还是要谢谢你的思维。
SRCS000 2012-12-18
  • 打赏
  • 举报
 回复
引用 16 楼 bbbbbben 的回复:
SQL code?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939……
看看数据。缺少了和要求不符合.感觉好像有点难实现啊。
山城忙碌人 2012-12-18
  • 打赏
  • 举报
 回复
楼主，如果你使用的是sqlserver05以上，利用row_number(partition by customerID order by totalPrice) 就可以解决！ select a1,出现次数,时间 from ( SELECT *,ROW_NUMBER() OVER(PARTITION BY [时间] ORDER BY [出现次数] DESC ) AS row_id FROM #tb ) as a where row_id <3
-晴天 2012-12-17
  • 打赏
  • 举报
 回复
乱套了! select A1,dt,ct from( select row_number()over(partition by a1,dt order by ct desc) as rn,* from( SELECT A1 ,COUNT(A1)ct, CONVERT(VARCHAR(8),A2,112)dt FROM A GROUP BY A1 ,CONVERT(VARCHAR(8),A2,112) HAVING COUNT(A1)>2 )T)T2 WHERE rn<=2
-晴天 2012-12-17
  • 打赏
  • 举报
 回复
你把我写的程序改了啊.......... select A1,A2,ct from( select row_number()over(partition by a1,dt order by ct desc) as rn,* from( SELECT A1 ,COUNT(A1)ct, CONVERT(VARCHAR(8),A2,112)dt FROM A GROUP BY A1 ,CONVERT(VARCHAR(8),A2,112) HAVING COUNT(A1)>2 )T)T2 WHERE rn<=2
加载更多回复（17）
业务逻辑写在SQL中还是代码中问题
作为一名程序员，逻辑思维应该是基于代码实现逻辑，既然不是专业DBA，不应将过多的经历用在如何通过复杂SQL去实现功能，而是尽量保证SQL的简单，易于迁移与理解，通过代码层面去实现功能。要知道的是需求是多变的，可以发现SQL越简单，在应对多变需求越是有利。执迷于通过大SQL，复杂SQL实现业务逻辑，只会给后人留坑，最终导致这个SQL没人敢改，不敢动。...
SQLServer复杂SQL逻辑实现
一、问题 如下图，已有表a与表b的数据，如何通过SQL语句变成表c？表a是UserName相同时，根据Month降序汇总Salary数据；表b是UserName对应SumSalary的上限值；表c是从表a中取出表b上限值以下的分录，若分录的SumSalary值大于b表的Salary，则拆分出刚好汇总等于b表的Salary数。 表A的SQL语句： create table b( Use
sql常用的语句及其逻辑
最近经常使用sql，这里将平时用的不多的记录一下。常用的逻辑也会不多的总结。 常用语句： SELECT DISTINCT：表中一个列可能会包含多个重复值，distinct关键词用于返回唯一不同的值。 SELECT TOP：规定要返回的记录的数目。对于拥有数千条记录的大型表来说，是非常有用的。 BETWEEN：操作符选取介于两个值之间的数据范围内的值，这些值可以是数值、文本或者日期。 IN...
自己实现一个SQL解析引擎
SQL的解析引擎包括查询编译与查询优化和查询的运行，主要包括3个步骤： 查询分析： 制定逻辑查询计划（优化相关） 制定物理查询计划（优化相关） 查询分析： 将SQL语句表示成某种有用的语法树. 制定逻辑查询计划： 把语法树转换成一个关系代数表达式或者类似的结构，这个结构通常称作逻辑计划。 制定物理查询计划：把逻辑计划转换成物理查询计划，要求指定操作执行的顺序，每一步使用的算法，操作之间的传递方式等。
SQL 语法里的逻辑判断
在日常代码编程中，我们会经常用到if-else进行逻辑判断，在SQL中是否也能进行逻辑判断呢，答案是肯定的！在SQL语句中有以下三个函数可以进行逻辑判断。 1. decode 函数： 语法 decode(条件，值1，返回值1，值2，返回值2，…值n,返回值n，缺省值) –缺省值可以省略 函数含义 表示如果 条件 等于 值1 时，DECODE函数的结果 返回值1 ,…,如果不等于任何一个条件值，则返回缺省值。 即：decode(value, if1, then1, if2,then2, if3,then3,
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章