34,590
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-18 14:28:26
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([DAY] [int],[用户] [nvarchar](10))
INSERT INTO [A]
SELECT '1','A' UNION ALL
SELECT '1','B' UNION ALL
SELECT '1','A' UNION ALL
SELECT '1','C' UNION ALL
SELECT '2','A' UNION ALL
SELECT '2','B' UNION ALL
SELECT '2','B' UNION ALL
SELECT '2','D' UNION ALL
SELECT '2','D'
--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([ID] [int],[用户] [nvarchar](10))
INSERT INTO [B]
SELECT '1','A' UNION ALL
SELECT '2','B' UNION ALL
SELECT '3','C' UNION ALL
SELECT '4','D' UNION ALL
SELECT '5','E'
--SELECT * FROM [A]
--SELECT * FROM [B]
-->SQL查询如下:
SELECT [DAY],用户,
次数=(SELECT count(1) FROM A WHERE A.用户=T.用户 AND A.[DAY]=T.[DAY])
FROM (
SELECT *
FROM (SELECT DISTINCT [DAY] FROM A) A,B
) T
/*
DAY 用户 次数
----------- ---------- -----------
1 A 2
1 B 1
1 C 1
1 D 0
1 E 0
2 A 1
2 B 2
2 C 0
2 D 2
2 E 0
(10 行受影响)
*/
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A([DAY] INT,[用户] VARCHAR(10))
INSERT INTO A
SELECT 1 ,'A' UNION ALL
SELECT 1 ,'B' UNION ALL
SELECT 1 ,'A' UNION ALL
SELECT 1 ,'C' UNION ALL
SELECT 2 ,'A' UNION ALL
SELECT 2 ,'B' UNION ALL
SELECT 2 ,'B' UNION ALL
SELECT 2 ,'D' UNION ALL
SELECT 2 ,'D'
CREATE TABLE B(ID INT,[用户] VARCHAR(10))
INSERT INTO B
SELECT 1 ,'A' UNION ALL
SELECT 2 ,'B' UNION ALL
SELECT 3 ,'C' UNION ALL
SELECT 4 ,'D' UNION ALL
SELECT 5 ,'E'
SELECT
T.[DAY],B.[用户],COUNT(A.[DAY])
FROM B
CROSS JOIN(SELECT DISTINCT [DAY] FROM A) T
LEFT JOIN A ON T.[DAY]=A.[DAY] AND B.[用户]=A.[用户]
GROUP BY T.[DAY],B.[用户]
ORDER BY T.[DAY],B.[用户]
/*
1 A 2
1 B 1
1 C 1
1 D 0
1 E 0
2 A 1
2 B 2
2 C 0
2 D 2
2 E 0
*/