求一个sql逻辑的实现
请问情况如下
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语句实现就是理想了。请各位大神教教小弟吧。谢谢
------解决方案--------------------這樣?
USE test
GO
-->生成表tb
if object_id('tb') is not null
drop table tb
Go
Create table tb([A1] smallint,[当天出现次数] smallint,[A2] datetime)
Insert into tb
Select 1,8,'2012-12-14'
Union all Select 1,6,'2012-12-15'
Union all Select 2,4,'2012-12-15'
Union all Select 4,6,'2012-12-15'
--Union all Select 4,8,'2012-12-15' -- test
--Union all Select 4,4,'2012-12-15' -- test
SELECT
*
FROM tb AS a
WHERE A1>2
AND EXISTS(SELECT 1 FROM tb AS x
WHERE x.A2=a.A2
AND x.A1>2
AND x.当天出现次数>=a.当天出现次数
HAVING COUNT(1) IN (1,2)
)
ORDER BY a.A2,a.当天出现次数 DESC
/*
A1 当天出现次数 A2
---- ------------ --------------------------
4 6 2012-12-15 00:00:00.000
*/
Go
------解决方案--------------------排序上再加一个.
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