求一SQL查询语句

[xhblzj]

第一个查询结果:
表1:
机构 人口
1501 102
1503 205
1504 421
1521 502
******
若干条
第二个查询结果:
表2:
机构 已婚
1501 45
1503 85
1504 77
1521 508
******
和第一表条数相同,机构相同
求一查询语句,将两查询合并,得到如下结果:
表3:
机构 人口 已婚
1501 102 45
1503 205 85
1504 421 77
1521 502 508
******
我想应该能行的,请教各位了

[liangCK]

[Quote=引用 8 楼 dawugui 的回复:]
SQL codeselect 机构 , 人口 = sum(人口) , 已婚 = sum(已婚) from
(
select * from tb1
union all
select * from tb2
) t
group by 机构
[/Quote]

你的是错误的.

[dawugui]

[Quote=引用 7 楼 liangCK 的回复:]
SQL code内连接
select a.机构,a.人品,b.已婚
from tb1 a
join tb2 b
on a.机构=b.机构

左连接
select a.机构,a.人品,b.已婚
from tb1 a
left outer join tb2 b
on a.机构=b.机构

全外连接
select isnull(a.机构,b,机构) 机构, isnull(a.人品,0) 人品, isnull(b.已婚,0) 已婚
from tb1 a
full join tb2 b
on a.机构=b.机构
[/Quote]
直接union all就行了.

[liangCK]

[Quote=引用 12 楼 dawugui 的回复:]
引用 7 楼 liangCK 的回复:
SQL code内连接
select a.机构,a.人品,b.已婚
from tb1 a
join tb2 b
on a.机构=b.机构

左连接
select a.机构,a.人品,b.已婚
from tb1 a
left outer join tb2 b
on a.机构=b.机构

全外连接
select isnull(a.机构,b,机构) 机构, isnull(a.人品,0) 人品, isnull(b.已婚,0) 已婚
from tb1 a
full join tb2 b
on a.机构=b.机构

直接union all就行…
[/Quote]

按你这样..行个P

[dawugui]

select 机构 , 人口 = sum(人口) , 已婚 = sum(已婚) from
(
select * , 已婚 = 0 from tb1
union all
select 机构,人口 = 0 , 已婚 from tb2
) t
group by 机构

看错了点,这样行了.

[liangCK]

[Quote=引用 14 楼 dawugui 的回复:]
select 机构 , 人口 = sum(人口) , 已婚 = sum(已婚) from
(
select * , 已婚 = 0 from tb1
union all
select 机构,人口 = 0 , 已婚 from tb2
) t
group by 机构

看错了点,这样行了.
[/Quote]

你也老龟烧胡子了..

[xhblzj]

两个表是两个SQL查询结果,如何把它表示为表

[dawugui]

[Quote=引用 16 楼 xhblzj 的回复:]
两个表是两个SQL查询结果,如何把它表示为表
[/Quote]
小梁子,解答一下.

[橘子香水]

select a.机构,a.人数,b.已婚
from
(
select 机构,人数=count(*)
from 表1
group by 机构
)a,
(select 机构,已婚=count(*)
from 表1
where 条件=已婚
group by 机构
)b
where a.机构=b.机构

[yas531]

select a.jigou a.renkou b.yihui from biao1 a biao2 b where a.jigou=b.gou

[xhblzj]

第一查询:
SELECT TOP 100 PERCENT bansc.dbo.jigou.jigouID AS 编码,
bansc.dbo.jigou.jigou AS 机构名, SUM(WIS.T_HouseAccount.SumPeople)
AS 人口合计, SUM(WIS.T_HouseAccount.MinoritySumpeople) AS 少数民族,
SUM(WIS.T_HouseAccount.Inflow) AS 流入, SUM(WIS.T_HouseAccount.MinorityInflow)
AS 流入民族, SUM(WIS.T_HouseAccount.Outflow) AS 流出,
SUM(WIS.T_HouseAccount.MinorityOutflow) AS 流出民族, COUNT(*) AS 户合计
FROM bansc.dbo.jigou RIGHT OUTER JOIN
WIS.T_HouseAccount ON
bansc.dbo.jigou.jigouID = SUBSTRING(WIS.T_HouseAccount.HouseAccountID, 0,
15) OR
bansc.dbo.jigou.jigouID = SUBSTRING(WIS.T_HouseAccount.HouseAccountID, 0,
13) OR
bansc.dbo.jigou.jigouID = SUBSTRING(WIS.T_HouseAccount.HouseAccountID, 0, 10)
GROUP BY bansc.dbo.jigou.jigouID, bansc.dbo.jigou.jigou,
WIS.T_HouseAccount.TrashFlag
HAVING (WIS.T_HouseAccount.TrashFlag <> 1)
ORDER BY bansc.dbo.jigou.jigouID

结果:
编码 机构名 人口合计 少数民族 流入 流入民族 流出 流出民族 户合计
152801021 西环办 27661 1888 2592 169 45 6 9210
152801021001 一居民 3615 234 395 25 0 0 1255
15280102100101 一居一组 310 19 33 3 0 0 104
15280102100102 一居二组 397 15 53 4 0 0 140
15280102100103 一居三组 762 33 63 2 0 0 265
15280102100104 一居四组 189 12 16 2 0 0 66
15280102100105 一居五组 346 17 27 2 0 0 118
15280102100106 一居六组 451 16 65 1 0 0 154
15280102100107 一居七组 742 88 85 3 0 0 261

第二个查询:
SELECT TOP 100 PERCENT bansc.dbo.jigou.jigouID AS 编码,
bansc.dbo.jigou.jigou AS 机构名, COUNT(*) AS 户
FROM bansc.dbo.jigou RIGHT OUTER JOIN
(SELECT *
FROM (SELECT WIS.T_WomanAccount.HouseAccountID AS 户编,
WIS.T_WomanAccount.WomanID AS 育编,
WIS.T_WomanAccount.Name AS 女名,
WIS.T_WomanAccount.Birthday AS 女出生,
WIS.T_Nation.Name AS 女民族, WIS.T_Marriage.Name AS 男名,
WIS.T_Marriage.Birthday AS 男出生, T_Nation_1.Name AS 男民族,
WIS.T_InhabitancyKind.Name AS 居住性质,
WIS.T_WomanAccount.HouseAddress AS 女户籍地,
WIS.T_HouseAccount.SumPeople AS 人口,
WIS.T_HouseAccount.MinoritySumpeople AS 少民,
WIS.T_HouseAccount.Inflow AS 流入,
WIS.T_MarriageStatus.Name AS 女婚,
WIS.T_ContraceptStatus.Name AS 措施,
WIS.T_Contracept.BeginDate AS 措施日期,
WIS.T_WomanAccount.Inhabitation AS 女居住地
FROM WIS.T_WomanAccount LEFT OUTER JOIN
WIS.T_Marriage ON WIS.T_Marriage.TrashFlag = 0 AND
SUBSTRING(WIS.T_WomanAccount.MarriageStatusID, 1, 1) = '2' AND
WIS.T_WomanAccount.WomanID = WIS.T_Marriage.WomanID AND
WIS.T_WomanAccount.MarryDate = WIS.T_Marriage.MarriageDate LEFT
OUTER JOIN
WIS.T_Contracept ON
WIS.T_WomanAccount.WomanID = WIS.T_Contracept.WomanID AND
WIS.T_Contracept.ContraceptID =
(SELECT MAX(ContraceptID) AS ContraceptID
FROM WIS.T_Contracept
WHERE TrashFlag <> 1 AND
WomanID = WIS.T_WomanAccount.WomanID)
LEFT OUTER JOIN
WIS.T_HouseAccount ON
WIS.T_WomanAccount.HouseAccountID = WIS.T_HouseAccount.HouseAccountID
LEFT OUTER JOIN
WIS.T_Nation ON
WIS.T_WomanAccount.NationID = WIS.T_Nation.NationID LEFT OUTER
JOIN
WIS.T_MarriageStatus ON
WIS.T_WomanAccount.MarriageStatusID = WIS.T_MarriageStatus.MarriageStatusID
LEFT OUTER JOIN
WIS.T_InhabitancyKind ON
WIS.T_WomanAccount.InhabitancyKindID = WIS.T_InhabitancyKind.InhabitancyKindID
LEFT OUTER JOIN
WIS.T_ContraceptStatus ON
WIS.T_Contracept.ContraceptStatusID = WIS.T_ContraceptStatus.ContraceptStatusID
LEFT OUTER JOIN
WIS.T_Nation T_Nation_1 ON
WIS.T_Marriage.NationID = T_Nation_1.NationID
WHERE (WIS.T_WomanAccount.TrashFlag <> 1) AND
(WIS.T_Marriage.TrashFlag <> 1 OR
WIS.T_Marriage.TrashFlag IS NULL) AND
(WIS.T_Contracept.TrashFlag <> 1 OR
WIS.T_Contracept.TrashFlag IS NULL) AND
(WIS.T_HouseAccount.TrashFlag <> 1 OR
WIS.T_HouseAccount.TrashFlag IS NULL) AND
(WIS.T_MarriageStatus.Name <> '未婚') AND
(WIS.T_InhabitancyKind.Name LIKE '%流入%')) DERIVEDTBL)
DERIVEDTBL ON bansc.dbo.jigou.jigouID = SUBSTRING(DERIVEDTBL.户编, 0, 15) OR
bansc.dbo.jigou.jigouID = SUBSTRING(DERIVEDTBL.户编, 0, 13) OR
bansc.dbo.jigou.jigouID = SUBSTRING(DERIVEDTBL.户编, 0, 10)
GROUP BY bansc.dbo.jigou.jigouID, bansc.dbo.jigou.jigou
ORDER BY bansc.dbo.jigou.jigouID

结果:
编码 机构名 户
152801021 西环办 683
152801021001 一居民 106
15280102100101 一居一组 10
15280102100102 一居二组 16
15280102100103 一居三组 19
15280102100104 一居四组 4
15280102100105 一居五组 5
15280102100106 一居六组 17
15280102100107 一居七组 20
15280102100108 一居八组 15
如何把两个合并显示在一个编码和机构名下,附结果只复制了部分.
谢谢各位楼主!!!!

[fan_52024115]

给出原始的几个表不是更好吗

[xhblzj]

一百大几十个表,我十再打不出来,再有怕影响人家的版权
对不起,不好意思

[pt1314917]

[Quote=引用 16 楼 xhblzj 的回复:]
两个表是两个SQL查询结果,如何把它表示为表
[/Quote]

select a.*,b.已婚 from
(把第一个查询语句放在这里)a,
(把第二个查询语句放在这里)b
where a.机构=b.机构

[tianhuo_soft]

select a.*,b.已婚 from
表1 a,
表2 b
where a.机构=b.机构

[lucky]

[Quote=引用 17 楼 dawugui 的回复:]
引用 16 楼 xhblzj 的回复:
两个表是两个SQL查询结果,如何把它表示为表

小梁子,解答一下.
[/Quote]
等着看你啦，偶相信你行滴

[GeniusMing]

select 表1.机构,人口,已婚
from 表1,表2
where (表1.机构=表2.机构)

[sjj_nuli]

a代表第一个表

b代表第二个表

select a.*,b.已婚 from a,b
where a.机构=b.机构

[lupingui]

Select 人口信息.机构,人口信息.人口,已婚人数.已婚 From
(
 Select 机构,人员 From 表1
)AS 人中信息
Inner Join 
(
 Select 机构,已婚 From 表2
)AS 已婚人数 On 已婚人数.机构=人口信息.机构

[waresiu]

select a.*,b.已婚
from 表1 as a
left join 表2 as b on a.机构=b.机构

[fkuk2]

把两个表联接起来，不就搞定了