---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-12-30 09:06:24 -- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) -- Dec 28 2012 20:23:12 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([gid] int,[goodsno] varchar(2),[pro] varchar(3)) insert [A] select 1,'bb','dan' union all select 2,'cc','xie' union all select 3,'dd','wan' union all select 4,'ee','ws' union all select 5,'vv','sa' if object_id('[B]') is not null drop table [B] go create table [B]([gid] int,[pro] varchar(4),[dw] varchar(1)) insert [B] select 1,'dan','s' union all select 2,'xie','s' union all select 6,'xiao','s' --------------开始查询-------------------------- SELECT GID,MAX(GOODSNO)goodsno ,MAX(PRO)PRO FROM ( SELECT GID,GOODSNO,PRO FROM a UNION ALL SELECT GID,PRO GOODSNO,DW PRO FROM B)A GROUP BY GID----------------结果---------------------------- /* GID goodsno PRO ----------- ------- ---- 1 dan s 2 xie xie 3 dd wan 4 ee ws 5 vv sa 6 xiao s */
--> 测试数据:[a] if object_id('[a]') is not null drop table [a] go create table [a]([gid] int,[goodsno] varchar(6),[pro] varchar(6)) insert [a] select 1,'bb','dan' union all select 2,'cc','xie' union all select 3,'dd','wan' union all select 4,'ee','ws' union all select 5,'vv','sa'--> 测试数据:[b] if object_id('[b]') is not null drop table [b] go create table [b]([gid] int,[pro] varchar(6),[dw] varchar(6)) insert [b] select 1,'dan','s' union all select 2,'xie','s' union all select 6,'xiao','s' select * from a union all select * from b where gid not in ( select gid from a ) gid goodsno pro ----------- ------- ------ 1 bb dan 2 cc xie 3 dd wan 4 ee ws 5 vv sa 6 xiao s(6 行受影响)
好像我表述的有点问题 这样呢?各位能帮我继续看下嘛? gid goodsno intercode pro 1 bb 555 dan 2 cc 666 xie 3 dd 777 wan 4 ee 888 ws 5 vv 999 sa gid pro intercode dw 1 dan 555 s 2 xie 666 s 6 xiao 222 s结果: gid goodsno intercode pro 1 bb 555 dan 2 cc 666 xie 3 dd 777 wan 4 ee 888 ws 5 vv 999 sa 6 222 xiao
好像我表述的有点问题 这样呢?各位能帮我继续看下嘛? gid goodsno intercode pro 1 bb 555 dan 2 cc 666 xie 3 dd 777 wan 4 ee 888 ws 5 vv 999 sa gid pro intercode dw 1 dan 555 s 2 xie 666 s 6 xiao 222 s结果: gid goodsno intercode pro 1 bb 555 dan 2 cc 666 xie 3 dd 777 wan 4 ee 888 ws 5 vv 999 sa 6 222 xiao
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-12-30 09:30:25 -- Version: -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) -- Dec 28 2012 20:23:12 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) -- ---------------------------------------------------------------- --> 测试数据:[A] if object_id('[A]') is not null drop table [A] go create table [A]([gid] int,[goodsno] varchar(2),[intercode] int,[pro] varchar(3)) insert [A] select 1,'bb',555,'dan' union all select 2,'cc',666,'xie' union all select 3,'dd',777,'wan' union all select 4,'ee',888,'ws' union all select 5,'vv',999,'sa' if object_id('[B]') is not null drop table [B] go create table [B]([gid] int,[pro] varchar(4),[intercode] int,[dw] varchar(1)) insert [B] select 1,'dan',555,'s' union all select 2,'xie',666,'s' union all select 6,'xiao',222,'s' --------------开始查询-------------------------- SELECT gid,MAX(goodsno)goodsno,MAX(intercode)intercode,MAX(pro)pro FROM ( select * from [A] UNION ALL select gid,NULL [goodsno],intercode,pro from [B] )a GROUP BY gid----------------结果---------------------------- /* gid goodsno intercode pro ----------- ------- ----------- ---- 1 bb 555 dan 2 cc 666 xie 3 dd 777 wan 4 ee 888 ws 5 vv 999 sa 6 NULL 222 xiao */
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-30 09:06:24
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([gid] int,[goodsno] varchar(2),[pro] varchar(3))
insert [A]
select 1,'bb','dan' union all
select 2,'cc','xie' union all
select 3,'dd','wan' union all
select 4,'ee','ws' union all
select 5,'vv','sa'
if object_id('[B]') is not null drop table [B]
go
create table [B]([gid] int,[pro] varchar(4),[dw] varchar(1))
insert [B]
select 1,'dan','s' union all
select 2,'xie','s' union all
select 6,'xiao','s'
--------------开始查询--------------------------
SELECT GID,MAX(GOODSNO)goodsno ,MAX(PRO)PRO
FROM (
SELECT GID,GOODSNO,PRO
FROM a
UNION ALL
SELECT GID,PRO GOODSNO,DW PRO
FROM B)A
GROUP BY GID----------------结果----------------------------
/*
GID goodsno PRO
----------- ------- ----
1 dan s
2 xie xie
3 dd wan
4 ee ws
5 vv sa
6 xiao s
*/
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([gid] int,[goodsno] varchar(6),[pro] varchar(6))
insert [a]
select 1,'bb','dan' union all
select 2,'cc','xie' union all
select 3,'dd','wan' union all
select 4,'ee','ws' union all
select 5,'vv','sa'--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([gid] int,[pro] varchar(6),[dw] varchar(6))
insert [b]
select 1,'dan','s' union all
select 2,'xie','s' union all
select 6,'xiao','s' select * from a
union all
select * from b where gid not in ( select gid from a )
gid goodsno pro
----------- ------- ------
1 bb dan
2 cc xie
3 dd wan
4 ee ws
5 vv sa
6 xiao s(6 行受影响)
gid goodsno intercode pro
1 bb 555 dan
2 cc 666 xie
3 dd 777 wan
4 ee 888 ws
5 vv 999 sa
gid pro intercode dw
1 dan 555 s
2 xie 666 s
6 xiao 222 s结果:
gid goodsno intercode pro
1 bb 555 dan
2 cc 666 xie
3 dd 777 wan
4 ee 888 ws
5 vv 999 sa
6 222 xiao
好像我表述的有点问题 这样呢?各位能帮我继续看下嘛?
gid goodsno intercode pro
1 bb 555 dan
2 cc 666 xie
3 dd 777 wan
4 ee 888 ws
5 vv 999 sa
gid pro intercode dw
1 dan 555 s
2 xie 666 s
6 xiao 222 s结果:
gid goodsno intercode pro
1 bb 555 dan
2 cc 666 xie
3 dd 777 wan
4 ee 888 ws
5 vv 999 sa
6 222 xiao
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-30 09:30:25
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([gid] int,[goodsno] varchar(2),[intercode] int,[pro] varchar(3))
insert [A]
select 1,'bb',555,'dan' union all
select 2,'cc',666,'xie' union all
select 3,'dd',777,'wan' union all
select 4,'ee',888,'ws' union all
select 5,'vv',999,'sa'
if object_id('[B]') is not null drop table [B]
go
create table [B]([gid] int,[pro] varchar(4),[intercode] int,[dw] varchar(1))
insert [B]
select 1,'dan',555,'s' union all
select 2,'xie',666,'s' union all
select 6,'xiao',222,'s'
--------------开始查询--------------------------
SELECT gid,MAX(goodsno)goodsno,MAX(intercode)intercode,MAX(pro)pro
FROM (
select * from [A]
UNION ALL
select gid,NULL [goodsno],intercode,pro
from [B]
)a
GROUP BY gid----------------结果----------------------------
/*
gid goodsno intercode pro
----------- ------- ----------- ----
1 bb 555 dan
2 cc 666 xie
3 dd 777 wan
4 ee 888 ws
5 vv 999 sa
6 NULL 222 xiao
*/