select * from ( select d.code,A.name,d.type,C.type_name,d.op_name from d join A on d.code=A.book_code join C on A.type=C.type )TT where op_name='张三'
select book_code as code,name as code_name,1 as type,'书籍' as type_name op_name from c join a on c.code=a.book_code where c.type=1 and c.op_name='张三' uion select vedio_code,vedio_name as code_name,2 as type,'光盘' as type_name op_name from c join a on c.code=a.book_code where c.type=1 and c.op_name='张三' 你这种设计不灵活的,类型表是固定的,根据类型的不同取不同的表里的数据,所以查询中是不需要类型表的。 其实如果book和vedio属性差别不大的话,可以放到一个表中,与type关联,这样写语句就比较好写了。
--> 生成测试数据: @a DECLARE @a TABLE (book_code VARCHAR(4),name VARCHAR(5)) INSERT INTO @a SELECT 'A001','书名1' UNION ALL SELECT 'A002','书名2' UNION ALL SELECT 'A003','书名3' UNION ALL SELECT 'A004','书名4'
--> 生成测试数据: @b DECLARE @b TABLE (vedio_code VARCHAR(4),vedio_name VARCHAR(5)) INSERT INTO @b SELECT 'B001','光碟1' UNION ALL SELECT 'B002','光碟2' UNION ALL SELECT 'B003','光碟3' UNION ALL SELECT 'B004','光碟4'
--> 生成测试数据: @c DECLARE @c TABLE (type INT,type_name VARCHAR(4)) INSERT INTO @c SELECT 1,'书籍' UNION ALL SELECT 2,'光盘'
--> 生成测试数据: @d DECLARE @d TABLE (code VARCHAR(4),type INT,op_name VARCHAR(4)) INSERT INTO @d SELECT 'A001',1,'张三' UNION ALL SELECT 'A002',1,'张三' UNION ALL SELECT 'A003',1,'李四' UNION ALL SELECT 'B002',2,'张三' UNION ALL SELECT 'B003',2,'张三' UNION ALL SELECT 'B004',2,'李四'--SQL查询如下:SELECT A.code, ISNULL(C.name,D.vedio_name) AS code_name, B.type_name, A.type, A.op_name FROM @d AS A JOIN @c AS B ON A.Type = B.Type LEFT JOIN @a AS C ON A.code = C.book_code LEFT JOIN @b AS D ON A.code = D.vedio_code WHERE A.op_name = '张三'/* code code_name type_name type op_name ---- --------- --------- ----------- ------- A001 书名1 书籍 1 张三 A002 书名2 书籍 1 张三 B002 光碟2 光盘 2 张三 B003 光碟3 光盘 2 张三(4 row(s) affected) */
select book_code as code,name as code_name,1 as type,'书籍' as type_name, op_name from c join a on c.code=a.book_code where c.type=1 and c.op_name='张三' uion select vedio_code,vedio_name as code_name,2 as type,'光盘' as type_name, op_name from c join a on c.code=a.book_code where c.type=1 and c.op_name='张三'
DECLARE @a TABLE (book_code VARCHAR(4),name VARCHAR(5)) INSERT INTO @a SELECT 'A001','书名1' UNION ALL SELECT 'A002','书名2' UNION ALL SELECT 'A003','书名3' UNION ALL SELECT 'A004','书名4'
--> 生成测试数据: @b DECLARE @b TABLE (vedio_code VARCHAR(4),vedio_name VARCHAR(5)) INSERT INTO @b SELECT 'B001','光碟1' UNION ALL SELECT 'B002','光碟2' UNION ALL SELECT 'B003','光碟3' UNION ALL SELECT 'B004','光碟4'
--> 生成测试数据: @c DECLARE @c TABLE (type INT,type_name VARCHAR(4)) INSERT INTO @c SELECT 1,'书籍' UNION ALL SELECT 2,'光盘'
--> 生成测试数据: @d DECLARE @d TABLE (code VARCHAR(4),type INT,op_name VARCHAR(4)) INSERT INTO @d SELECT 'A001',1,'张三' UNION ALL SELECT 'A002',1,'张三' UNION ALL SELECT 'A003',1,'李四' UNION ALL SELECT 'B002',2,'张三' UNION ALL SELECT 'B003',2,'张三' UNION ALL SELECT 'B004',2,'李四'select * from ( select d.code,A.name,d.type,C.type_name,d.op_name from @d d left join (select book_code,name from @a union all select vedio_code,vedio_name from @b ) A on d.code=A.book_code left join @c C on d.type=C.type ) TT where op_name='张三'code name type type_name op_name ---- ----- ----------- --------- ------- A001 书名1 1 书籍 张三 A002 书名2 1 书籍 张三 B002 光碟2 2 光盘 张三 B003 光碟3 2 光盘 张三
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-09-10 15:01:16 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([book_code] varchar(4),[name] varchar(5)) insert [a] select 'A001','书名1' union all select 'A002','书名2' union all select 'A003','书名3' union all select 'A004','书名4' --> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([vedio_code] varchar(4),[vedio_name] varchar(5)) insert [b] select 'B001','光碟1' union all select 'B002','光碟2' union all select 'B003','光碟3' union all select 'B004','光碟4' --> 测试数据:[c] if object_id('[c]') is not null drop table [c] go create table [c]([type] int,[type_name] varchar(4)) insert [c] select 1,'书籍' union all select 2,'光盘' --> 测试数据:[d] if object_id('[d]') is not null drop table [d] go create table [d]([code] varchar(4),[type] int,[op_name] varchar(4)) insert [d] select 'A001',1,'张三' union all select 'A002',1,'张三' union all select 'A003',1,'李四' union all select 'B002',2,'张三' union all select 'B003',2,'张三' union all select 'B004',2,'李四' --------------开始查询-------------------------- select a.code, isnull(c.name,d.vedio_name) as code_name, b.type_name, a.type, a.op_name from d as a join c as b on a.type = b.type left join a as c on a.code = c.book_code left join b as d on a.code = d.vedio_code where a.op_name = '张三' ----------------结果---------------------------- /*code code_name type_name type op_name ---- --------- --------- ----------- ------- A001 书名1 书籍 1 张三 A002 书名2 书籍 1 张三 B002 光碟2 光盘 2 张三 B003 光碟3 光盘 2 张三(4 行受影响) */
(
select d.code,A.name,d.type,C.type_name,d.op_name from d join A on d.code=A.book_code join C on A.type=C.type
)TT
where op_name='张三'
select book_code as code,name as code_name,1 as type,'书籍' as type_name op_name
from c join a on c.code=a.book_code where c.type=1 and c.op_name='张三'
uion
select vedio_code,vedio_name as code_name,2 as type,'光盘' as type_name op_name
from c join a on c.code=a.book_code where c.type=1 and c.op_name='张三'
你这种设计不灵活的,类型表是固定的,根据类型的不同取不同的表里的数据,所以查询中是不需要类型表的。
其实如果book和vedio属性差别不大的话,可以放到一个表中,与type关联,这样写语句就比较好写了。
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-10 14:59:22
-------------------------------------------
--> 生成测试数据: @a
DECLARE @a TABLE (book_code VARCHAR(4),name VARCHAR(5))
INSERT INTO @a
SELECT 'A001','书名1' UNION ALL
SELECT 'A002','书名2' UNION ALL
SELECT 'A003','书名3' UNION ALL
SELECT 'A004','书名4'
--> 生成测试数据: @b
DECLARE @b TABLE (vedio_code VARCHAR(4),vedio_name VARCHAR(5))
INSERT INTO @b
SELECT 'B001','光碟1' UNION ALL
SELECT 'B002','光碟2' UNION ALL
SELECT 'B003','光碟3' UNION ALL
SELECT 'B004','光碟4'
--> 生成测试数据: @c
DECLARE @c TABLE (type INT,type_name VARCHAR(4))
INSERT INTO @c
SELECT 1,'书籍' UNION ALL
SELECT 2,'光盘'
--> 生成测试数据: @d
DECLARE @d TABLE (code VARCHAR(4),type INT,op_name VARCHAR(4))
INSERT INTO @d
SELECT 'A001',1,'张三' UNION ALL
SELECT 'A002',1,'张三' UNION ALL
SELECT 'A003',1,'李四' UNION ALL
SELECT 'B002',2,'张三' UNION ALL
SELECT 'B003',2,'张三' UNION ALL
SELECT 'B004',2,'李四'--SQL查询如下:SELECT A.code,
ISNULL(C.name,D.vedio_name) AS code_name,
B.type_name,
A.type,
A.op_name
FROM @d AS A
JOIN @c AS B
ON A.Type = B.Type
LEFT JOIN @a AS C
ON A.code = C.book_code
LEFT JOIN @b AS D
ON A.code = D.vedio_code
WHERE A.op_name = '张三'/*
code code_name type_name type op_name
---- --------- --------- ----------- -------
A001 书名1 书籍 1 张三
A002 书名2 书籍 1 张三
B002 光碟2 光盘 2 张三
B003 光碟3 光盘 2 张三(4 row(s) affected)
*/
select book_code as code,name as code_name,1 as type,'书籍' as type_name, op_name from c join a on c.code=a.book_code where c.type=1 and c.op_name='张三' uion select vedio_code,vedio_name as code_name,2 as type,'光盘' as type_name, op_name from c join a on c.code=a.book_code where c.type=1 and c.op_name='张三'
INSERT INTO @a
SELECT 'A001','书名1' UNION ALL
SELECT 'A002','书名2' UNION ALL
SELECT 'A003','书名3' UNION ALL
SELECT 'A004','书名4'
--> 生成测试数据: @b
DECLARE @b TABLE (vedio_code VARCHAR(4),vedio_name VARCHAR(5))
INSERT INTO @b
SELECT 'B001','光碟1' UNION ALL
SELECT 'B002','光碟2' UNION ALL
SELECT 'B003','光碟3' UNION ALL
SELECT 'B004','光碟4'
--> 生成测试数据: @c
DECLARE @c TABLE (type INT,type_name VARCHAR(4))
INSERT INTO @c
SELECT 1,'书籍' UNION ALL
SELECT 2,'光盘'
--> 生成测试数据: @d
DECLARE @d TABLE (code VARCHAR(4),type INT,op_name VARCHAR(4))
INSERT INTO @d
SELECT 'A001',1,'张三' UNION ALL
SELECT 'A002',1,'张三' UNION ALL
SELECT 'A003',1,'李四' UNION ALL
SELECT 'B002',2,'张三' UNION ALL
SELECT 'B003',2,'张三' UNION ALL
SELECT 'B004',2,'李四'select * from
(
select d.code,A.name,d.type,C.type_name,d.op_name from @d d left join
(select book_code,name from @a
union all
select vedio_code,vedio_name from @b
) A
on d.code=A.book_code left join @c C on d.type=C.type
)
TT
where op_name='张三'code name type type_name op_name
---- ----- ----------- --------- -------
A001 书名1 1 书籍 张三
A002 书名2 1 书籍 张三
B002 光碟2 2 光盘 张三
B003 光碟3 2 光盘 张三
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-10 15:01:16
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([book_code] varchar(4),[name] varchar(5))
insert [a]
select 'A001','书名1' union all
select 'A002','书名2' union all
select 'A003','书名3' union all
select 'A004','书名4'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([vedio_code] varchar(4),[vedio_name] varchar(5))
insert [b]
select 'B001','光碟1' union all
select 'B002','光碟2' union all
select 'B003','光碟3' union all
select 'B004','光碟4'
--> 测试数据:[c]
if object_id('[c]') is not null drop table [c]
go
create table [c]([type] int,[type_name] varchar(4))
insert [c]
select 1,'书籍' union all
select 2,'光盘'
--> 测试数据:[d]
if object_id('[d]') is not null drop table [d]
go
create table [d]([code] varchar(4),[type] int,[op_name] varchar(4))
insert [d]
select 'A001',1,'张三' union all
select 'A002',1,'张三' union all
select 'A003',1,'李四' union all
select 'B002',2,'张三' union all
select 'B003',2,'张三' union all
select 'B004',2,'李四'
--------------开始查询--------------------------
select
a.code,
isnull(c.name,d.vedio_name) as code_name,
b.type_name,
a.type,
a.op_name
from
d as a
join c as b
on
a.type = b.type
left join a as c
on
a.code = c.book_code
left join b as d
on
a.code = d.vedio_code
where
a.op_name = '张三'
----------------结果----------------------------
/*code code_name type_name type op_name
---- --------- --------- ----------- -------
A001 书名1 书籍 1 张三
A002 书名2 书籍 1 张三
B002 光碟2 光盘 2 张三
B003 光碟3 光盘 2 张三(4 行受影响)
*/