select a1,a2,b1,b2 from a,b where a.a1=b.b2 and a.a1='a' and b.b1='b'
select a.*,b.* from ta a join tb b on a.编号=b.编号 where a.合同号=isnull(@合同号,a.合同号) and b.品名=isnull(@品名,b.品名)union用来合并2个结构完全相同的结果集,并去除重复记录。
写掉2个字…… select a.*,b.* from ta a join tb b on a.编号=b.合同编号 where a.合同号=isnull(@合同号,a.合同号) and b.品名=isnull(@品名,b.品名)
create proc P_test @合同号 nvarchar(20), @品名 nvarchar(50) as select a.*,b.* from ta a,tb b where a.A1=b.B2 and a.合同号=ISNULL(NULLIF(@合同号,''),a.合同号) AND b.品名=ISNULL(NULLIF(@品名,''),b.品名)
create proc findcontract @contractnum int=null, @staffName nvarchar(50)=null as begin select contractid,staffName,Num from @a a,@b b where a.id=b.id and (@contractnum is null or @contractnum=[合同编号]) and (@staffName is null or @staffName=[品名]) end
select a.col1,a.col2...from tb1 a,tb2 b where a.col1='a'and b.col1='b'
select * from table1 a,table2 b where a.a1='1' and b.B2='b' AND A.A1=B.B2
CREATE TABLE #A (A1 VARCHAR(20),A2 VARCHAR(20))INSERT INTO #A SELECT 'a','A2' INSERT INTO #A SELECT 'a','14' INSERT INTO #A SELECT 'b','33' INSERT INTO #A SELECT 'c','22' CREATE TABLE #B (B1 VARCHAR(20),B2 VARCHAR(20))INSERT INTO #B SELECT 'b','hy' INSERT INTO #B SELECT 'b','45' INSERT INTO #B SELECT 'b','fe' INSERT INTO #B SELECT 'h','jk' SELECT IDENTITY(INT,1,1) SORT,A1,A2 INTO #C FROM #A WHERE A1='a'SELECT IDENTITY(INT,1,1) SORT,B1,B2 INTO #D FROM #B WHERE B1='b' SELECT A1,A2,B1,B2 FROM #C A FULL JOIN #D B ON (A.SORT=B.SORT) DROP TABLE #A DROP TABLE #B DROP TABLE #C DROP TABLE #D/** a A2 b hy a 14 b 45 NULL NULL b fe
--a、b表没有关联?这样?
select * from a,b where a1='a' and b1='b'
这样?
B表字段分别为编号index,合同编号contractindex,涉及货物品名name与数量amount 其中合同编号contractindex指向A表的编号index我现在想实现的就是根据用户的两个条件,一个合同编号,一个品名,把两个表里面的对应两条记录都取出来我对union不太明白,不知道使用union是否可以实现
你最好貼點數據,及你想要的結果
select a.*,b.* from ta a,tb b where a.A1=b.B2 a.a1='a' and b.b1='b'
select * from ta a
inner join tb b on a.a1=b.b2
where a.a1='a' and b.b1='b'
001 GXHT20081001 2008-10-01
002 GXHT20081002 2008-10-02
003 GXHT20081003 2008-10-03表B数据如下编号 合同编号 品名 数量
001 001 焦煤 3000
002 002 精煤 4000
003 003 中煤 5000比如这两个表,A表表示合同基本内容,B表表示合同涉及货物内容我现在在界面上放一个合同号combobox和一个品名combobox然后让客户在里面选择具体内容来查询点击查询按钮调用我的一个存储过程,存储过程里面的select语句最后根据用户选择的查询条件来获取对应记录用户不一定所有的查询条件都选择,可能只选择合同编号而不选择品名,也可能只选择品名而不选择合同编号
a.a1='a' and b.b1='b'
select a.*,b.*
from ta a join tb b on a.编号=b.编号
where a.合同号=isnull(@合同号,a.合同号) and b.品名=isnull(@品名,b.品名)union用来合并2个结构完全相同的结果集,并去除重复记录。
select a.*,b.*
from ta a join tb b on a.编号=b.合同编号
where a.合同号=isnull(@合同号,a.合同号) and b.品名=isnull(@品名,b.品名)
@合同号 nvarchar(20),
@品名 nvarchar(50)
as
select a.*,b.* from ta a,tb b where a.A1=b.B2
and a.合同号=ISNULL(NULLIF(@合同号,''),a.合同号)
AND b.品名=ISNULL(NULLIF(@品名,''),b.品名)
create proc findcontract
@contractnum int=null,
@staffName nvarchar(50)=null
as
begin
select contractid,staffName,Num from @a a,@b b
where a.id=b.id
and (@contractnum is null or @contractnum=[合同编号])
and (@staffName is null or @staffName=[品名])
end
select a.col1,a.col2...from tb1 a,tb2 b where a.col1='a'and b.col1='b'
INSERT INTO #A SELECT 'a','14'
INSERT INTO #A SELECT 'b','33'
INSERT INTO #A SELECT 'c','22'
CREATE TABLE #B (B1 VARCHAR(20),B2 VARCHAR(20))INSERT INTO #B SELECT 'b','hy'
INSERT INTO #B SELECT 'b','45'
INSERT INTO #B SELECT 'b','fe'
INSERT INTO #B SELECT 'h','jk'
SELECT IDENTITY(INT,1,1) SORT,A1,A2 INTO #C FROM #A WHERE A1='a'SELECT IDENTITY(INT,1,1) SORT,B1,B2 INTO #D FROM #B WHERE B1='b'
SELECT A1,A2,B1,B2 FROM #C A FULL JOIN #D B ON (A.SORT=B.SORT)
DROP TABLE #A
DROP TABLE #B
DROP TABLE #C
DROP TABLE #D/**
a A2 b hy
a 14 b 45
NULL NULL b fe