有两个表A和B
A表:
ID H
1 200
2 300
3 989
4 785
5 657
6 948B表:
ID Q
1 154
3 657
5 754现在需要建立一个视图C,包括这两个表
视图C
ID H Q
1 200 154
2 300
3 989 657
4 785
5 657 754
6 948不知视图语句应该怎么写,请高手指点!
A表:
ID H
1 200
2 300
3 989
4 785
5 657
6 948B表:
ID Q
1 154
3 657
5 754现在需要建立一个视图C,包括这两个表
视图C
ID H Q
1 200 154
2 300
3 989 657
4 785
5 657 754
6 948不知视图语句应该怎么写,请高手指点!
解决方案 »
- 莱鸟问题啊,
- 高分请教bcp导入数据问题,顶者有分
- 只查询重复的记录,要怎么做啊?
- sql2008 链接服务器的时候出错,急求帮忙解决!!
- SQL Server Audit中获得客户端IP地址?
- 谁我分析一下, 我分析了一天了,还是不懂
- 这个语句应该怎么写?
- 如何保存sqlserver中scripts 中的print 到文件?
- SqlServer2000的问题----非常急(再线等待!!!)
- 1000万数据,用自增id做条件,sum某个字段很快,用time做条件sum某个字段就很慢
- 请教一个sql语句问题
- update 表1的字段B='aa',字段c='bb',当表1字段A的值=表2字段A的值
AS
SELECT
A.ID,
A.H,
B.Q
FROM tbA AS A
LEFT JOIN
tbB AS B
ON A.ID=B.ID
GO
-- Author: liangCK 小梁
-- Date : 2008-11-17 14:43:06
---------------------------------
--> 生成测试数据: [tbA]
IF OBJECT_ID('[tbA]') IS NOT NULL DROP TABLE [tbA]
CREATE TABLE [tbA] (ID INT,H INT)
INSERT INTO [tbA]
SELECT 1,200 UNION ALL
SELECT 2,300 UNION ALL
SELECT 3,989 UNION ALL
SELECT 4,785 UNION ALL
SELECT 5,657 UNION ALL
SELECT 6,948
--> 生成测试数据: [tbB]
IF OBJECT_ID('[tbB]') IS NOT NULL DROP TABLE [tbB]
CREATE TABLE [tbB] (ID INT,Q INT)
INSERT INTO [tbB]
SELECT 1,154 UNION ALL
SELECT 3,657 UNION ALL
SELECT 5,754--SQL查询如下:GOCREATE VIEW v_T
AS
SELECT
A.ID,
A.H,
B.Q
FROM tbA AS A
LEFT JOIN
tbB AS B
ON A.ID=B.ID
GOSELECT * FROM v_TGO
DROP TABLE tbA,tbB
DROP VIEW v_T/*
ID H Q
----------- ----------- -----------
1 200 154
2 300 NULL
3 989 657
4 785 NULL
5 657 754
6 948 NULL(6 行受影响)
*/
-- Author: liangCK 小梁
-- Date : 2008-11-17 14:43:06
---------------------------------
--> 生成测试数据: [tbA]
IF OBJECT_ID('[tbA]') IS NOT NULL DROP TABLE [tbA]
CREATE TABLE [tbA] (ID INT,H INT)
INSERT INTO [tbA]
SELECT 1,200 UNION ALL
SELECT 2,300 UNION ALL
SELECT 3,989 UNION ALL
SELECT 4,785 UNION ALL
SELECT 5,657 UNION ALL
SELECT 6,948
--> 生成测试数据: [tbB]
IF OBJECT_ID('[tbB]') IS NOT NULL DROP TABLE [tbB]
CREATE TABLE [tbB] (ID INT,Q INT)
INSERT INTO [tbB]
SELECT 1,154 UNION ALL
SELECT 3,657 UNION ALL
SELECT 5,754--SQL查询如下:GOCREATE VIEW v_T
AS
SELECT
A.ID,
A.H,
ISNULL(CAST(B.Q AS VARCHAR),'') AS Q
FROM tbA AS A
LEFT JOIN
tbB AS B
ON A.ID=B.ID
GOSELECT * FROM v_TGO
DROP TABLE tbA,tbB
DROP VIEW v_T/*
ID H Q
----------- ----------- ------------------------------
1 200 154
2 300
3 989 657
4 785
5 657 754
6 948 (6 行受影响)
*/
insert into a values(1 , 200 )
insert into a values(2 , 300 )
insert into a values(3 , 989 )
insert into a values(4 , 785 )
insert into a values(5 , 657 )
insert into a values(6 , 948 )
create table b(ID int, Q int)
insert into b values(1 , 154 )
insert into b values(3 , 657 )
insert into b values(5 , 754 )select a.* , isnull(cast(b.Q as varchar), '') q from a left join b on a.id = b.iddrop table a , b/*
ID H q
----------- ----------- ------------------------------
1 200 154
2 300
3 989 657
4 785
5 657 754
6 948 (所影响的行数为 6 行)*/
insert into a values(1 , 200 )
insert into a values(2 , 300 )
insert into a values(3 , 989 )
insert into a values(4 , 785 )
insert into a values(5 , 657 )
insert into a values(6 , 948 )
create table b(ID int, Q int)
insert into b values(1 , 154 )
insert into b values(3 , 657 )
insert into b values(5 , 754 )--search
select a.* , isnull(cast(b.Q as varchar), '') q from a left join b on a.id = b.id--create view
go
create view my_view as select a.* , isnull(cast(b.Q as varchar), '') q from a left join b on a.id = b.id
goselect * from my_viewdrop table a , b
drop view my_view/*
ID H q
----------- ----------- ------------------------------
1 200 154
2 300
3 989 657
4 785
5 657 754
6 948 (所影响的行数为 6 行)*/