select * from AA A where not exists(select * from AA where Date=A.Date and userID<A.UserID)
SELECT USERID,MAX(DATE)DATE FROM TB GROUP BY USERID
SELECT MAX(USERID),CONVERT(VARCHAR(10),DATE,120) FROM TB GROUP BY CONVERT(VARCHAR(10),DATE,120)
---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2010-02-01 17:42:04 -- Version: -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) -- Aug 6 2000 00:57:48 -- Copyright (c) 1988-2000 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:#A if object_id('tempdb.dbo.#A') is not null drop table #A go create table #A([userID] int,[Date] datetime) insert #A select 125,'2010-1-31' union all select 123,'2010-1-31' union all select 123,'2010-2-1' union all select 124,'2010-2-1' union all select 125,'2010-2-1' union all select 126,'2010-2-1' --------------开始查询--------------------------SELECT MAX(USERID),CONVERT(VARCHAR(10),DATE,120) FROM #A GROUP BY CONVERT(VARCHAR(10),DATE,120) ----------------结果---------------------------- /* (所影响的行数为 6 行) ----------- ---------- 125 2010-01-31 126 2010-02-01(所影响的行数为 2 行) */
select userid,max(date) as date from tb where ...--这里写你的时间段 group by userid
select count(userid),a.[date] from (select userid,min([date]) as [date] from #temp group by userid) as a group by a.[date]
userid不分大小,max(userID)这个好像不对吧
暂时就想到2次group by......下班,闪人-_,-
select count(userid),a.[date] from (select userid,min([date]) as [date] from #temp group by userid) as a group by a.[date]
-- Author :SQL77(只为思齐老) -- Date :2010-02-01 17:42:04 -- Version: -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) -- Aug 6 2000 00:57:48 -- Copyright (c) 1988-2000 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:#A if object_id('tempdb.dbo.#A') is not null drop table #A go create table #A([userID] int,[Date] datetime) insert #A --select 124,'2010-1-30' union all select 125,'2010-1-31' union all select 123,'2010-1-31' union all select 123,'2010-2-1' union all select 124,'2010-2-1' union all select 125,'2010-2-1' union all select 126,'2010-2-1'SELECT DISTINCT CONVERT(VARCHAR(10),Date,120)DATE, (SELECT COUNT(*) FROM #A T1 WHERE CONVERT(VARCHAR(10),Date,120)=CONVERT(VARCHAR(10),T.Date,120) AND USERID NOT IN (SELECT USERID FROM #A WHERE CONVERT(VARCHAR(10),Date,120)<CONVERT(VARCHAR(10),T1.Date,120)) ) FROM #A T (所影响的行数为 6 行)DATE ---------- ----------- 2010-01-31 2 2010-02-01 2(所影响的行数为 2 行)
-- Author :SQL77(只为思齐老)
-- Date :2010-02-01 17:42:04
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#A
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A([userID] int,[Date] datetime)
insert #A
select 125,'2010-1-31' union all
select 123,'2010-1-31' union all
select 123,'2010-2-1' union all
select 124,'2010-2-1' union all
select 125,'2010-2-1' union all
select 126,'2010-2-1'
--------------开始查询--------------------------SELECT MAX(USERID),CONVERT(VARCHAR(10),DATE,120) FROM #A GROUP BY CONVERT(VARCHAR(10),DATE,120)
----------------结果----------------------------
/* (所影响的行数为 6 行)
----------- ----------
125 2010-01-31
126 2010-02-01(所影响的行数为 2 行)
*/
select userid,max(date) as date from tb
where ...--这里写你的时间段
group by userid
结果不是要显示 id和日期而是 日期和id的个数按上边我写的那个例子的结果应该是2010-1-31 2
2010-2-1 2
select count(userid),a.[date] from
(select userid,min([date]) as [date] from #temp group by userid) as a
group by a.[date]
userid不分大小,max(userID)这个好像不对吧
select count(userid),a.[date] from
(select userid,min([date]) as [date] from #temp group by userid) as a
group by a.[date]
-- Author :SQL77(只为思齐老)
-- Date :2010-02-01 17:42:04
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#A
if object_id('tempdb.dbo.#A') is not null drop table #A
go
create table #A([userID] int,[Date] datetime)
insert #A
--select 124,'2010-1-30' union all
select 125,'2010-1-31' union all
select 123,'2010-1-31' union all
select 123,'2010-2-1' union all
select 124,'2010-2-1' union all
select 125,'2010-2-1' union all
select 126,'2010-2-1'SELECT DISTINCT CONVERT(VARCHAR(10),Date,120)DATE,
(SELECT COUNT(*) FROM #A T1
WHERE CONVERT(VARCHAR(10),Date,120)=CONVERT(VARCHAR(10),T.Date,120)
AND USERID NOT IN
(SELECT USERID FROM #A WHERE CONVERT(VARCHAR(10),Date,120)<CONVERT(VARCHAR(10),T1.Date,120))
) FROM #A T
(所影响的行数为 6 行)DATE
---------- -----------
2010-01-31 2
2010-02-01 2(所影响的行数为 2 行)