例:表A
主键是A
A M Z
__________________
1 你 99
2 我 88
3 他 77
表B
主键是B
B N
__________________
4 中国
5 *
6 香港
表C
主键是A,B,C
A B C D
________________________
1 4 7 3
2 5 8 6
3 6 9 9
现在画面上grid要求显示是这样的,没有数据就显示为空。
________________________________________________
你 我 他
中国 99
* 88
香港 99
谢谢
7 个解决方案
#1
交叉表查询
#2
CREATE TABLE A
(A INT,
M VARCHAR(5),
Z INT
)
INSERT INTO A
SELECT 1,'你',99 UNION ALL
SELECT 2,'我',88 UNION ALL
SELECT 3,'他',77
CREATE TABLE B
(B INT,
N VARCHAR(5)
)
INSERT INTO B
SELECT 4,'中国' UNION ALL
SELECT 5,'*' UNION ALL
SELECT 6,'香港'
CREATE TABLE C
(
A INT,
B INT,
C INT,
D INT
)
INSERT INTO C
SELECT 1,4,7,3 UNION ALL
SELECT 2,5,8,6 UNION ALL
SELECT 3,6,9,9
DECLARE @S VARCHAR(4000)
SET @S='SELECT D.A,E.N,'
SELECT @S=@S+'CASE M WHEN '''+M+''' THEN Z END AS '+M+',' FROM
(SELECT DISTINCT A,M FROM A) AA
SET @S=LEFT(@S,LEN(@S)-1)+' FROM A D'
SET @S=@S+' INNER JOIN (SELECT C.A,B.N FROM B INNER JOIN C ON B.B=C.B) E ON D.A=E.A'
EXEC(@S)
--结果
A N 你 我 他
----------- ----- ----------- ----------- -----------
1 中国 99 NULL NULL
2 * NULL 88 NULL
3 香港 NULL NULL 77
(A INT,
M VARCHAR(5),
Z INT
)
INSERT INTO A
SELECT 1,'你',99 UNION ALL
SELECT 2,'我',88 UNION ALL
SELECT 3,'他',77
CREATE TABLE B
(B INT,
N VARCHAR(5)
)
INSERT INTO B
SELECT 4,'中国' UNION ALL
SELECT 5,'*' UNION ALL
SELECT 6,'香港'
CREATE TABLE C
(
A INT,
B INT,
C INT,
D INT
)
INSERT INTO C
SELECT 1,4,7,3 UNION ALL
SELECT 2,5,8,6 UNION ALL
SELECT 3,6,9,9
DECLARE @S VARCHAR(4000)
SET @S='SELECT D.A,E.N,'
SELECT @S=@S+'CASE M WHEN '''+M+''' THEN Z END AS '+M+',' FROM
(SELECT DISTINCT A,M FROM A) AA
SET @S=LEFT(@S,LEN(@S)-1)+' FROM A D'
SET @S=@S+' INNER JOIN (SELECT C.A,B.N FROM B INNER JOIN C ON B.B=C.B) E ON D.A=E.A'
EXEC(@S)
--结果
A N 你 我 他
----------- ----- ----------- ----------- -----------
1 中国 99 NULL NULL
2 * NULL 88 NULL
3 香港 NULL NULL 77
#3
DECLARE @S VARCHAR(4000)
SET @S='SELECT E.N,'
SELECT @S=@S+'CASE M WHEN '''+M+''' THEN Z END AS '+M+',' FROM
(SELECT DISTINCT A,M FROM A) AA
SET @S=LEFT(@S,LEN(@S)-1)+' FROM A D'
SET @S=@S+' INNER JOIN (SELECT C.A,B.N FROM B INNER JOIN C ON B.B=C.B) E ON D.A=E.A'
EXEC(@S)
--前面没有A列的
SET @S='SELECT E.N,'
SELECT @S=@S+'CASE M WHEN '''+M+''' THEN Z END AS '+M+',' FROM
(SELECT DISTINCT A,M FROM A) AA
SET @S=LEFT(@S,LEN(@S)-1)+' FROM A D'
SET @S=@S+' INNER JOIN (SELECT C.A,B.N FROM B INNER JOIN C ON B.B=C.B) E ON D.A=E.A'
EXEC(@S)
--前面没有A列的
#4
我是通过函数来实现的
#5
用函数好!我以前用临时表,现在写了一个函数,就可以全部用函数来处理。
不过我是在java下写的。
不过我是在java下写的。
#6
create table A(A int, M varchar(10), Z int)
insert A select 1, '你', 99
union all select 2, '我', 88
union all select 3, '他', 77
go
create table B(B int, N varchar(10))
insert B select 4, '中国'
union all select 5, '*'
union all select 6, '香港'
go
create table C(A int, B int, C int, D int)
insert C select 1, 4, 7, 3
union all select 2, 5, 8, 6
union all select 3, 6, 9, 9
declare @sql varchar(8000)
set @sql='select B.N,'
select @sql=@sql+quotename(M)+'=case when A='+rtrim(A)+' then '+quotename(rtrim(Z), '''')+' else '''' end,'
from A
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from B left join C on B.B=C.B'
exec(@sql)
--result
N 你 我 他
---------- ---- ---- ----
中国 99
* 88
香港 77
insert A select 1, '你', 99
union all select 2, '我', 88
union all select 3, '他', 77
go
create table B(B int, N varchar(10))
insert B select 4, '中国'
union all select 5, '*'
union all select 6, '香港'
go
create table C(A int, B int, C int, D int)
insert C select 1, 4, 7, 3
union all select 2, 5, 8, 6
union all select 3, 6, 9, 9
declare @sql varchar(8000)
set @sql='select B.N,'
select @sql=@sql+quotename(M)+'=case when A='+rtrim(A)+' then '+quotename(rtrim(Z), '''')+' else '''' end,'
from A
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from B left join C on B.B=C.B'
exec(@sql)
--result
N 你 我 他
---------- ---- ---- ----
中国 99
* 88
香港 77
#7
select B.N as N,k.你,k.我,k.他 from B,C,(select A,sum(case M when '你' then Z else null end ) as 你,sum(case M when '我' then Z else null end ) as 我,sum(case M when '他' then Z else null end ) as 他 from A group by a ) k where K.a=c.a and b.b=c.b
#1
交叉表查询
#2
CREATE TABLE A
(A INT,
M VARCHAR(5),
Z INT
)
INSERT INTO A
SELECT 1,'你',99 UNION ALL
SELECT 2,'我',88 UNION ALL
SELECT 3,'他',77
CREATE TABLE B
(B INT,
N VARCHAR(5)
)
INSERT INTO B
SELECT 4,'中国' UNION ALL
SELECT 5,'*' UNION ALL
SELECT 6,'香港'
CREATE TABLE C
(
A INT,
B INT,
C INT,
D INT
)
INSERT INTO C
SELECT 1,4,7,3 UNION ALL
SELECT 2,5,8,6 UNION ALL
SELECT 3,6,9,9
DECLARE @S VARCHAR(4000)
SET @S='SELECT D.A,E.N,'
SELECT @S=@S+'CASE M WHEN '''+M+''' THEN Z END AS '+M+',' FROM
(SELECT DISTINCT A,M FROM A) AA
SET @S=LEFT(@S,LEN(@S)-1)+' FROM A D'
SET @S=@S+' INNER JOIN (SELECT C.A,B.N FROM B INNER JOIN C ON B.B=C.B) E ON D.A=E.A'
EXEC(@S)
--结果
A N 你 我 他
----------- ----- ----------- ----------- -----------
1 中国 99 NULL NULL
2 * NULL 88 NULL
3 香港 NULL NULL 77
(A INT,
M VARCHAR(5),
Z INT
)
INSERT INTO A
SELECT 1,'你',99 UNION ALL
SELECT 2,'我',88 UNION ALL
SELECT 3,'他',77
CREATE TABLE B
(B INT,
N VARCHAR(5)
)
INSERT INTO B
SELECT 4,'中国' UNION ALL
SELECT 5,'*' UNION ALL
SELECT 6,'香港'
CREATE TABLE C
(
A INT,
B INT,
C INT,
D INT
)
INSERT INTO C
SELECT 1,4,7,3 UNION ALL
SELECT 2,5,8,6 UNION ALL
SELECT 3,6,9,9
DECLARE @S VARCHAR(4000)
SET @S='SELECT D.A,E.N,'
SELECT @S=@S+'CASE M WHEN '''+M+''' THEN Z END AS '+M+',' FROM
(SELECT DISTINCT A,M FROM A) AA
SET @S=LEFT(@S,LEN(@S)-1)+' FROM A D'
SET @S=@S+' INNER JOIN (SELECT C.A,B.N FROM B INNER JOIN C ON B.B=C.B) E ON D.A=E.A'
EXEC(@S)
--结果
A N 你 我 他
----------- ----- ----------- ----------- -----------
1 中国 99 NULL NULL
2 * NULL 88 NULL
3 香港 NULL NULL 77
#3
DECLARE @S VARCHAR(4000)
SET @S='SELECT E.N,'
SELECT @S=@S+'CASE M WHEN '''+M+''' THEN Z END AS '+M+',' FROM
(SELECT DISTINCT A,M FROM A) AA
SET @S=LEFT(@S,LEN(@S)-1)+' FROM A D'
SET @S=@S+' INNER JOIN (SELECT C.A,B.N FROM B INNER JOIN C ON B.B=C.B) E ON D.A=E.A'
EXEC(@S)
--前面没有A列的
SET @S='SELECT E.N,'
SELECT @S=@S+'CASE M WHEN '''+M+''' THEN Z END AS '+M+',' FROM
(SELECT DISTINCT A,M FROM A) AA
SET @S=LEFT(@S,LEN(@S)-1)+' FROM A D'
SET @S=@S+' INNER JOIN (SELECT C.A,B.N FROM B INNER JOIN C ON B.B=C.B) E ON D.A=E.A'
EXEC(@S)
--前面没有A列的
#4
我是通过函数来实现的
#5
用函数好!我以前用临时表,现在写了一个函数,就可以全部用函数来处理。
不过我是在java下写的。
不过我是在java下写的。
#6
create table A(A int, M varchar(10), Z int)
insert A select 1, '你', 99
union all select 2, '我', 88
union all select 3, '他', 77
go
create table B(B int, N varchar(10))
insert B select 4, '中国'
union all select 5, '*'
union all select 6, '香港'
go
create table C(A int, B int, C int, D int)
insert C select 1, 4, 7, 3
union all select 2, 5, 8, 6
union all select 3, 6, 9, 9
declare @sql varchar(8000)
set @sql='select B.N,'
select @sql=@sql+quotename(M)+'=case when A='+rtrim(A)+' then '+quotename(rtrim(Z), '''')+' else '''' end,'
from A
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from B left join C on B.B=C.B'
exec(@sql)
--result
N 你 我 他
---------- ---- ---- ----
中国 99
* 88
香港 77
insert A select 1, '你', 99
union all select 2, '我', 88
union all select 3, '他', 77
go
create table B(B int, N varchar(10))
insert B select 4, '中国'
union all select 5, '*'
union all select 6, '香港'
go
create table C(A int, B int, C int, D int)
insert C select 1, 4, 7, 3
union all select 2, 5, 8, 6
union all select 3, 6, 9, 9
declare @sql varchar(8000)
set @sql='select B.N,'
select @sql=@sql+quotename(M)+'=case when A='+rtrim(A)+' then '+quotename(rtrim(Z), '''')+' else '''' end,'
from A
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from B left join C on B.B=C.B'
exec(@sql)
--result
N 你 我 他
---------- ---- ---- ----
中国 99
* 88
香港 77
#7
select B.N as N,k.你,k.我,k.他 from B,C,(select A,sum(case M when '你' then Z else null end ) as 你,sum(case M when '我' then Z else null end ) as 我,sum(case M when '他' then Z else null end ) as 他 from A group by a ) k where K.a=c.a and b.b=c.b