• 主页
• 基础类
• 应用实例
• 新技术前沿

# 求救！很棘手的问题！

happy19781921 2003-12-11 11:38:55
select a.pm,a.gg,b.trsl as trsl,'0','0',c.gxhgl,round(b.trsl*c.gxhgl,0) ,
a.sjsl,c.hgldj,(a.sjsl- round(b.trsl*c.gxhgl,0))*c.hgldj From
(select pm,gg,gx,sum(hg) as sjsl from ysd where gx = '压' and zg = '锻压李班' and rq >= '2003-10-26' and rq <= '2003-11-25'group by pm,gg,gx)
as a inner Join
(select cpmc,cpgg,sum(sl) as trsl from xld where dybz = '锻压李班' and rq >= '2003-10-26' and rq<= '2003-11-25'group by cpmc,cpgg) as b
on a.pm = b.cpmc and a.gg = b.cpgg
Inner Join cpgx As c
on a.pm = c.pm and a.gg = c.gg where c.gx = '压'

pm,cpmc： 产品名称，gg,cpgg:产品规格，trsl: 投入数量，gxhgl: 工序合格率
sjsl: 实际数量，hgldj: 合格率单价 gx: 工序，zg:职工，dybz:锻压班组

ysd : 验收单 xld: 下料单

...全文
7 点赞 收藏 7

7 条回复

select a.pm,a.gg,b.trsl as trsl,'0','0'
,c.gxhgl,round(b.trsl*c.gxhgl,0)
,a.sjsl,c.hgldj,(a.sjsl- round(b.trsl*c.gxhgl,0))*c.hgldj
From(
select pm,gg,gx,sum(hg) as sjsl from ysd where gx = '压' and zg = '锻压李班' and rq >= '2003-10-26' and rq <= '2003-11-25'group by pm,gg,gx
) a full Join (
select cpmc,cpgg,sum(sl) as trsl from xld where dybz = '锻压李班' and rq >= '2003-10-26' and rq<= '2003-11-25'group by cpmc,cpgg
) b on a.pm = b.cpmc and a.gg = b.cpgg
Inner Join cpgx c on a.pm=c.pm and a.gg = c.gg where c.gx = '压'

dlpseeyou 2003-12-13
select d.cpmc,d.cpgg,d.trsl as trsl,'0','0',c.gxhgl,round(d.trsl*c.gxhgl,0) ,
d.sjsl,c.hgldj,(d.sjsl- round(d.trsl*c.gxhgl,0))*c.hgldj From
(select * from (select pm,gg,gx,sum(hg) as sjsl from ysd where gx = '压' and zg = '锻压李班' and rq >= '2003-10-26' and rq <= '2003-11-25'group by pm,gg,gx) as a
right Join (select cpmc,cpgg,sum(sl) as trsl from xld where dybz = '锻压李班' and rq >= '2003-10-26' and rq<= '2003-11-25'group by cpmc,cpgg) as b
on a.pm = b.cpmc and a.gg = b.cpgg) as d Inner Join cpgx As c
on d.cpmc = c.pm and d.cpgg = c.gg where c.gx = '压'

xzx760815 2003-12-12
1﹑用full join 可以解決把所有的數據都查出來。
2﹑后面兩個問題多用一下isnull()函數。如﹕isnull(數量,0)

happy19781921 2003-12-12

select d.cpmc,d.cpgg,d.trsl as trsl,'0','0',c.gxhgl,round(d.trsl*c.gxhgl,0) ,
d.sjsl,c.hgldj,(d.sjsl- round(d.trsl*c.gxhgl,0))*c.hgldj From
(select * from
(select pm,gg,gx,sum(hg) as sjsl from ysd where gx = '压' and zg = '锻压李班' and rq >= '2003-10-26' and rq <= '2003-11-25'group by pm,gg,gx) as a
right Join
(select cpmc,cpgg,sum(sl) as trsl from xld where dybz = '锻压李班' and rq >= '2003-10-26' and rq<= '2003-11-25'group by cpmc,cpgg) as b
on a.pm = b.cpmc and a.gg = b.cpgg) as d
Inner Join cpgx As c
on d.cpmc = c.pm and d.cpgg = c.gg where c.gx = '压'

happy19781921 2003-12-11

CrazyFor 2003-12-11

LEFT JOIN 或 LEFT OUTER JOIN。

RIGHT JOIN 或 RIGHT OUTER JOIN。

FULL JOIN 或 FULL OUTER JOIN。

Microsoft® SQL Server™ 2000 对在 FROM 子句中指定的外联接使用以下 SQL-92 关键字：

LEFT OUTER JOIN 或 LEFT JOIN

RIGHT OUTER JOIN 或 RIGHT JOIN

FULL OUTER JOIN 或 FULL JOIN
SQL Server 支持 SQL-92 外联接语法，以及在 WHERE 子句中使用 *= 和 =* 运算符指定外联接的旧式语法。由于 SQL-92 语法不容易产生歧义，而旧式 Transact-SQL 外联接有时会产生歧义，因此建议使用 SQL-92 语法。

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

au_fname au_lname pub_name
-------------------- ------------------------------ -----------------
Reginald Blotchet-Halls NULL
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Michael O'Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems

(23 row(s) affected)

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

au_fname au_lname pub_name
-------------------- ------------------------ --------------------
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL GGG&G
NULL NULL Lucerne Publishing
NULL NULL New Moon Books
NULL NULL Ramona Publishers
NULL NULL Scootney Books

(9 row(s) affected)

USE pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
ON s.title_id = t.title_id
AND s.qty > 50
ORDER BY s.stor_id ASC

stor_id qty title
------- ------ ---------------------------------------------------------
(null) (null) But Is It User Friendly?
(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior
Variations
(null) (null) Cooking with Computers: Surreptitious Balance Sheets
(null) (null) Emotional Security: A New Algorithm
(null) (null) Fifty Years in Buckingham Palace Kitchens
7066 75 Is Anger the Enemy?
(null) (null) Life Without Fear
(null) (null) Net Etiquette
(null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the
Mediterranean
(null) (null) Prolonged Data Deprivation: Four Case Studies
(null) (null) Secrets of Silicon Valley
(null) (null) Silicon Valley Gastronomic Treats
(null) (null) Straight Talk About Computers
(null) (null) Sushi, Anyone?
(null) (null) The Busy Executive's Database Guide
(null) (null) The Gourmet Microwave
(null) (null) The Psychology of Computer Cooking
(null) (null) You Can Combat Computer Stress!

(18 row(s) affected)

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

au_fname au_lname pub_name
-------------------- ---------------------------- --------------------
Reginald Blotchet-Halls NULL
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Michael O'Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL GGG&G
NULL NULL Lucerne Publishing
NULL NULL New Moon Books
NULL NULL Ramona Publishers
NULL NULL Scootney Books

(30 row(s) affected)

CrazyFor 2003-12-11
select a.pm,a.gg,b.trsl as trsl,'0','0',c.gxhgl,round(b.trsl*c.gxhgl,0) ,
a.sjsl,c.hgldj,(a.sjsl- round(b.trsl*c.gxhgl,0))*c.hgldj From
(select pm,gg,gx,sum(hg) as sjsl from ysd where gx = '压' and zg = '锻压李班' and rq >= '2003-10-26' and rq <= '2003-11-25'group by pm,gg,gx)
as a Left Join ----这里改成Left join or Right join
(select cpmc,cpgg,sum(sl) as trsl from xld where dybz = '锻压李班' and rq >= '2003-10-26' and rq<= '2003-11-25'group by cpmc,cpgg) as b
on a.pm = b.cpmc and a.gg = b.cpgg
Inner Join cpgx As c
on a.pm = c.pm and a.gg = c.gg where c.gx = '压'

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区