select A.*, D
from A join
(
select 1 as M, D from B1
union all select 3 as M, D from B3
union all select 5 as M, D from B5
) B on A.M=B.N
from A join
(
select 1 as M, D from B1
union all select 3 as M, D from B3
union all select 5 as M, D from B5
) B on A.M=B.N
解决方案 »
- 问一个关于数据库名的问题
- 求问一个SQL语句
- 站内搜索,数据量大,应该怎么做效率会比较高
- 求救:把sql server 2000与Dreamweaver连接后,浏览网页会提示错误:ADODB.Command (0x800A0BB9),求解决方法?
- 高分求触发器问题!!!
- 菜鸟提问,大家拿分(这个SQL语句怎么写)
- ft,菜鸟急问:为什么我的sqlserver2000不支持中文内容的查询,怎么设置才行阿
- 急急急急急急 关于Select 的问题!高分
- 关于数据库中随机抽取记录的问题
- 怎样更改oracle的internal用户的密码?
- 如何提高这个语句的速度,送分!
- 求一sql函数 能返回上周周末的日期时间值
from A join
(
select 1 as M, D from B1
union all select 3 as M, D from B3
union all select 5 as M, D from B5
) B on A.M=B.M --A.M=B.N
SELECT 1,2
UNION
ALL
SELECT 3,4
UNION ALL
SELECT 5,4CREATE TABLE B1 (D VARCHAR(20))
INSERT B1
SELECT 'AA'
UNION ALL
SELECT 'CC'CREATE TABLE B3 (D VARCHAR (20))
INSERT B3
SELECT 'BB'
CREATE TABLE B5 (D VARCHAR (20))
INSERT B5
SELECT 'DD'select A.*, D
from A join
(
select 1 as M, D from B1
union all select 3 as M, D from B3
union all select 5 as M, D from B5
) B on A.M=B.M
/*
M N D
----------- ----------- --------------------
1 2 AA
1 2 CC
3 4 BB
5 4 DD(4 行受影响)
*/
drop table A,B1,B3,B5
SELECT 1,2
UNION
ALL
SELECT 3,4
UNION ALL
SELECT 5,4CREATE TABLE B1 (D VARCHAR(20))
INSERT B1
SELECT 'AA'
UNION ALL
SELECT 'CC'CREATE TABLE B3 (D VARCHAR (20))
INSERT B3
SELECT 'BB'
CREATE TABLE B5 (D VARCHAR (20))
INSERT B5
SELECT 'DD'select A.*, D
from A join
(
select 1 as M, D from B1
union all select 3 as M, D from B3
union all select 5 as M, D from B5
) B on A.M=B.M
/*
M N D
----------- ----------- --------------------
1 2 AA
1 2 CC
3 4 BB
5 4 DD(4 行受影响)
*/
drop table A,B1,B3,B5
DROP TABLE A
DROP TABLE B1
DROP TABLE B3
DROP TABLE B5
DROP TABLE C
CREATE TABLE A(M INT,N INT)CREATE TABLE C(D VARCHAR)INSERT A
SELECT 1,2
UNION
ALL
SELECT 3,4
UNION ALL
SELECT 5,4CREATE TABLE B1 (D VARCHAR(20))
INSERT B1
SELECT 'AA'
UNION ALL
SELECT 'CC'CREATE TABLE B3 (D VARCHAR (20))
INSERT B3
SELECT 'BB'
CREATE TABLE B5 (D VARCHAR (20))
INSERT B5
SELECT 'DD'
select * from Aselect * from B1
select * from B3
select * from B5DECLARE @BranchCD int
DECLARE curFetchBranchCD CURSOR
FOR
SELECT
DISTINCT M
FROM AOPEN curFetchBranchCDFETCH NEXT FROM curFetchBranchCD
INTO @BranchCD
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @SQL VARCHAR(2000)
SET @SQL ='
INSERT C
SELECT
D
FROM
B'+CONVERT(VARCHAR(6),@BranchCD) PRINT (@SQL)
END
FETCH NEXT FROM curFetchBranchCD
INTO @BranchCDCLOSE curFetchBranchCD
DEALLOCATE curFetchBranchCDSELECT * FROM C
先把有效的表 全部找到,然后返回出来,
在分解返回的@tablist,在客户端 的 DataCommand的CommandText里面写进去。
如 @tablist按‘/’ 分割,取出每一个表,
string tablist = 返回的@tablist; ---这个需要代码写。 string [] split = tablist.Split(new Char [] {'/ '});
然后拼字符串到DataCommand的CommandText里。DataCommand.CommandText = 'Select A.M,A.N,'+ split[i]+'.D' ------------
create procedure Proc(@tablist varchar(100) out )
as
begin
declare @m int;
declare @tab varchar(100);
Declare Cur Curosor for
select M from A;
open cur;
fetch next cur into @m;
while @@fetch_status = 0
begin
@tab = rtrim('B'+cast(@m as varchar(10)));
IF OBJECT_ID ( '数据库名.' + @tab) ) IS NOT NULL
begin
@tablist= @tablist + '/' +object_name(OBJECT_ID ( '数据库名.' + @tab) ));
end
fetch next cur into @m;
end
select @tablist;
end