--> liangCK小梁 于2008-10-21
--> 生成测试数据: #A_test
IF OBJECT_ID('tempdb.dbo.#A_test') IS NOT NULL DROP TABLE #A_test
CREATE TABLE #A_test (info_id INT,depart_id VARCHAR(1),publish_time DATETIME)
INSERT INTO #A_test
SELECT 1,'a','2008-10-20 11:25:36.520' UNION ALL
SELECT 2,'a','2008-10-20 11:25:36.520' UNION ALL
SELECT 3,'a','2008-10-20 11:25:36.520' UNION ALL
SELECT 4,'b','2008-10-20 11:25:36.520' UNION ALL
SELECT 5,'b','2008-10-20 11:25:36.520' UNION ALL
SELECT 6,'b','2008-10-20 11:25:36.520' UNION ALL
SELECT 7,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 8,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 9,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 10,'b','2008-10-19 11:25:36.520' UNION ALL
SELECT 11,'b','2008-10-19 11:25:36.520' UNION ALL
SELECT 12,'b','2008-10-19 11:25:36.520' UNION ALL
SELECT 13,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 14,'a','2008-10-19 11:25:36.520'--SQL查询如下:SELECT depart_id,COUNT(*) AS cnt
FROM #A_test
GROUP BY depart_id;SELECT depart_id,COUNT(*) AS cnt
FROM #A_test
WHERE DATEDIFF(DAY,'2008-10-19',publish_time)=0
GROUP BY depart_id;/*
depart_id cnt
--------- -----------
a 8
b 6(2 行受影响)depart_id cnt
--------- -----------
a 5
b 3(2 行受影响)*/
--> 生成测试数据: #A_test
IF OBJECT_ID('tempdb.dbo.#A_test') IS NOT NULL DROP TABLE #A_test
CREATE TABLE #A_test (info_id INT,depart_id VARCHAR(1),publish_time DATETIME)
INSERT INTO #A_test
SELECT 1,'a','2008-10-20 11:25:36.520' UNION ALL
SELECT 2,'a','2008-10-20 11:25:36.520' UNION ALL
SELECT 3,'a','2008-10-20 11:25:36.520' UNION ALL
SELECT 4,'b','2008-10-20 11:25:36.520' UNION ALL
SELECT 5,'b','2008-10-20 11:25:36.520' UNION ALL
SELECT 6,'b','2008-10-20 11:25:36.520' UNION ALL
SELECT 7,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 8,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 9,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 10,'b','2008-10-19 11:25:36.520' UNION ALL
SELECT 11,'b','2008-10-19 11:25:36.520' UNION ALL
SELECT 12,'b','2008-10-19 11:25:36.520' UNION ALL
SELECT 13,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 14,'a','2008-10-19 11:25:36.520'--SQL查询如下:SELECT depart_id,COUNT(*) AS cnt
FROM #A_test
GROUP BY depart_id;SELECT depart_id,COUNT(*) AS cnt
FROM #A_test
WHERE DATEDIFF(DAY,'2008-10-19',publish_time)=0
GROUP BY depart_id;/*
depart_id cnt
--------- -----------
a 8
b 6(2 行受影响)depart_id cnt
--------- -----------
a 5
b 3(2 行受影响)*/
解决方案 »
- 请教删除多余的重复记录?
- ODBC中命名管道可以通,但TCP/IP就是联不通
- 算了, 送软件都没人要.
- 不明白:...From dbo.syscolumns A,dbo.syscolumns B
- 超难SQL,还不知道SQL能不能解决,请高手指点~~~~~~~~~~~~
- SQLServer2000库中有Text字段转Access出错??
- 动态构造多条件SQL查询语句
- 如何在选择结果集里面选择其中一段结果?
- 数据库查询,我一直没有解决的问题?????
- 问题:Sql Server2000中的自定义函数,如何在前端调用并取得返回值?
- 一个表中的数据依据另一个表中的条件得出汇总报表
- 如果判断字段是否允许为空?
--1、
SELECT depart_id,[sum]=COUNT(*) FROM A_test GROUP BY depart_id--2、
SELECT depart_id,[sum]=COUNT(*) FROM A_test WHERE DATEDIFF(dd,'2008-10-19',publish_time)=0
GROUP BY depart_id
通过一个sql:select info_id,depart_id,publish_time from B_test,查询出来的结果如下:
info_id depart_id publish_time
1 | a | 2008-10-20 11:25:36.520
2 | a | 2008-10-20 11:25:36.520
3 | a | 2008-10-20 11:25:36.520
4 | b | 2008-10-20 11:25:36.520
5 | b | 2008-10-20 11:25:36.520
6 | b | 2008-10-20 11:25:36.520
7 | a | 2008-10-19 11:25:36.520
8 | a | 2008-10-19 11:25:36.520
9 | a | 2008-10-19 11:25:36.520
10 | b | 2008-10-19 11:25:36.520
11 | b | 2008-10-19 11:25:36.520
12 | b | 2008-10-19 11:25:36.520
13 | a | 2008-10-19 11:25:36.520
14 | a | 2008-10-19 11:25:36.520-------------------
【1】现在要按照depart_id和publish_time进行统计
需要查询后的结果为:(sql语句怎么写)depart_id sum
a 8
b 6
------------------
【2】按照时间查询时[
where
convert(char(10),publish_time,20)>='2007-10-18'
AND convert(char(10),publish_time,20)<='2008-10-19' ]
需要查询后的结果为:(sql语句怎么写)
depart_id sum
a 5
b 3
--SET @d='2008-10-19' --赋值则传值, 不赋值则不传
SELECT depart_id,COUNT(*)
FROM a_test
WHERE DATEDIFF(dd,publish_time,ISNULL(@d,publish_time))=0
GROUP BY depart_id
--> 生成测试数据: #A_test
IF OBJECT_ID('tempdb.dbo.#A_test') IS NOT NULL DROP TABLE #A_test
CREATE TABLE #A_test (info_id INT,depart_id VARCHAR(1),publish_time DATETIME)
INSERT INTO #A_test
SELECT 1,'a','2008-10-20 11:25:36.520' UNION ALL
SELECT 2,'a','2008-10-20 11:25:36.520' UNION ALL
SELECT 3,'a','2008-10-20 11:25:36.520' UNION ALL
SELECT 4,'b','2008-10-20 11:25:36.520' UNION ALL
SELECT 5,'b','2008-10-20 11:25:36.520' UNION ALL
SELECT 6,'b','2008-10-20 11:25:36.520' UNION ALL
SELECT 7,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 8,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 9,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 10,'b','2008-10-19 11:25:36.520' UNION ALL
SELECT 11,'b','2008-10-19 11:25:36.520' UNION ALL
SELECT 12,'b','2008-10-19 11:25:36.520' UNION ALL
SELECT 13,'a','2008-10-19 11:25:36.520' UNION ALL
SELECT 14,'a','2008-10-19 11:25:36.520'--SQL查询如下:--1
SELECT depart_id,COUNT(*) AS cnt
FROM #A_test
GROUP BY depart_id;
--2
SELECT depart_id,COUNT(*) AS cnt
FROM #A_test
WHERE publish_time BETWEEN '2008-10-18' AND '2008-10-19 23:59:59'
GROUP BY depart_id;
/*
depart_id cnt
--------- -----------
a 8
b 6(2 行受影响)depart_id cnt
--------- -----------
a 5
b 3(2 行受影响)*/