我有两个表,表A和表B,其结构如下:
A(ID主键)
ID Name Address ClassID
1-1 张三 武汉 0123
1-2 王五 长沙 0123B(Number主键)
Number Value State
1-1 2 0
1-1 3 0
1-2 2 1数据库SQL Server2008
1、我现在需要把表A的ID,Name,ClassID和表B的Value提取出来(这两个表没有主外键关系)
2、首先检查State=0,然后提取表B的Number和Value,若1-1有多条数据,提取Value值最小那一条,按Number升序排
3、提取完表B中的数据后,根据条件Number查表A,最后返回数据我的想法,用临时表拼接这两个表的数据,然后返回~
不知大家有何高见!
SQL用得不熟,希望高人给个demo,不胜感激~~
A(ID主键)
ID Name Address ClassID
1-1 张三 武汉 0123
1-2 王五 长沙 0123B(Number主键)
Number Value State
1-1 2 0
1-1 3 0
1-2 2 1数据库SQL Server2008
1、我现在需要把表A的ID,Name,ClassID和表B的Value提取出来(这两个表没有主外键关系)
2、首先检查State=0,然后提取表B的Number和Value,若1-1有多条数据,提取Value值最小那一条,按Number升序排
3、提取完表B中的数据后,根据条件Number查表A,最后返回数据我的想法,用临时表拼接这两个表的数据,然后返回~
不知大家有何高见!
SQL用得不熟,希望高人给个demo,不胜感激~~
select a.ID,a.Name,a.ClassID,t.value
from A a inner join(
select Number,Value,State from(
select
px=row_number()over(partition by Number order by Value),*
from B where State=0)s where px=1)t
on t.Number=a.ID
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'A')
BEGIN
DROP TABLE A
END
GO
CREATE TABLE A
(
ID VARCHAR(10),
Name VARCHAR(10),
Address VARCHAR(10),
ClassID VARCHAR(10)
)
GO
INSERT INTO A
SELECT '1-1', '张三', '武汉', '0123' UNION
SELECT '1-2', '王五', '长沙', '0123'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'B')
BEGIN
DROP TABLE B
END
GO
CREATE TABLE B
(
Number VARCHAR(10),
Value INT,
State INT
)
GO
INSERT INTO B
SELECT '1-1', 2, 0 UNION
SELECT '1-1', 3, 0 UNION
SELECT '1-2', 2, 1GOSELECT ID,Name,ClassID,Value
FROM A,(SELECT Number,MIN(value) AS value FROM B WHERE State = 0 GROUP BY Number) AS C
WHERE A.ID = C.NumberID Name ClassID Value
1-1 张三 0123 2
if object_id('[tb_A]') is not null drop table [tb_A]
create table [tb_A]
insert [tb_A]
SELECT '1-1', '张三', '武汉', '0123' UNION ALL
SELECT '1-2', '王五', '长沙', '0123'
if object_id('[tb_B]') is not null drop table [tb_B]
create table [tb_B]
INSERT [tb_B]
SELECT '1-1', 2, 0 UNION ALL
SELECT '1-1', 3, 0 UNION ALL
SELECT '1-2', 2, 1SELECT ID,Name,ClassID,Value
FROM tb_A,(SELECT Number,MIN(value) AS value FROM tb_B WHERE State=0 GROUP BY Number) AS tb_C
WHERE tb_A.ID = tb_C.NumberID Name ClassID Value
1-1 张三 0123 2