我有下面两条语句,想要合并成一条~~~请高手帮忙~~~只要目的达到了,怎么改写都可以的~第一条:
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'overwrz1')
drop view overwrz1
go
create view overwrz1
as
SELECT STCD, TM, Z,
(SELECT stnm
FROM st_stbprp_b
WHERE stcd = st_river_r.stcd) AS name,
(SELECT WRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) AS wrz, Z -
(SELECT WRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) AS overwrz
FROM ST_RIVER_R
WHERE AND (Z -
(SELECT WRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) >= 0) AND (Z -
(SELECT gRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) < 0)
第二条:
SELECT TOP 100 PERCENT t.*
FROM overwrz1 t
WHERE (tm IN
(SELECT TOP 1 tm
FROM overwrz1
WHERE stcd = t.stcd AND tm > ' 2007-07-22' AND tm < '2007-07-23'
ORDER BY tm DESC))
ORDER BY STCD, tm感谢感谢~~~
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'overwrz1')
drop view overwrz1
go
create view overwrz1
as
SELECT STCD, TM, Z,
(SELECT stnm
FROM st_stbprp_b
WHERE stcd = st_river_r.stcd) AS name,
(SELECT WRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) AS wrz, Z -
(SELECT WRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) AS overwrz
FROM ST_RIVER_R
WHERE AND (Z -
(SELECT WRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) >= 0) AND (Z -
(SELECT gRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) < 0)
第二条:
SELECT TOP 100 PERCENT t.*
FROM overwrz1 t
WHERE (tm IN
(SELECT TOP 1 tm
FROM overwrz1
WHERE stcd = t.stcd AND tm > ' 2007-07-22' AND tm < '2007-07-23'
ORDER BY tm DESC))
ORDER BY STCD, tm感谢感谢~~~
合并就行了
注意列数
as
SELECT STCD, TM, Z,
(SELECT stnm
FROM st_stbprp_b
WHERE stcd = st_river_r.stcd) AS name,
(SELECT WRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) AS wrz, Z -
(SELECT WRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) AS overwrz
FROM ST_RIVER_R
WHERE AND (Z -
(SELECT WRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) >= 0) AND (Z -
(SELECT gRZ
FROM ST_RVFCCH_B
WHERE stcd = st_river_r.stcd) < 0)
union all
-- 第二条:
SELECT TOP 100 PERCENT t.*
FROM overwrz1 t
WHERE (tm IN
(SELECT TOP 1 tm
FROM overwrz1
WHERE stcd = t.stcd AND tm > ' 2007-07-22' AND tm < '2007-07-23'
ORDER BY tm DESC))
ORDER BY STCD, tm
第二条语句是从这个视图里选出符合某个条件的。用楼上各位所提到的“union all” ,在查询分析器里运行提示如下错误SELECT STCD, TM, Z,
行数在第一条语句的这个地方:
“create view overwrz1”