請問 SQL VIEW 建立

sql

2015evanotes 2021-05-21 09:12:59 ‧ 980 瀏覽

分享至

假設有一個表
id , path
1 , '2001/1/A'
2 , '2001/2/B'
3 , '2001/1/C'

path 代表年/月/公司
我要怎麼建立一個 VIEW 叫 report

用起來像這樣
select year,month,com from report where year = 2001 and com ='A'

窮嘶發發發 iT邦高手 1 級 ‧ 2021-05-21 09:30:08 檢舉

先寫好可以產生 select year,month,com from table 的語法
這樣就能很容易產生 VIEW，但如果表格不大 ( 低於 10萬筆 )，那建議直接SELECT就好，不需要開 VIEW

2015evanotes iT邦新手 1 級 ‧ 2021-05-21 10:34:49 檢舉

VIEW 是報表必須的

窮嘶發發發 iT邦高手 1 級 ‧ 2021-05-21 11:52:32 檢舉

我家的ERP沒有開VIEW，一樣出報表，VIEW只是視角，方便檢視資料而已，不是必須，如果你後台寫好查詢語法，一樣能出報表，所以我才說表格不大，不需要用VIEW，因為表格大，直接查詢，產生視表需要時間，可是VIEW已經做好了查詢的動作，可以節省很多時間，這才是VIEW必須的原因，不是因為報表

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 8 級 ‧ 2021-05-21 09:17:01

最佳解答

請參閱 : SQL Create View

CREATE TABLE TableX
(Id int,Path varchar(20));

INSERT INTO TableX 
VALUES
(1 , '2001/1/A'),
(2 , '2001/12/Bl'),
(3 , '2001/1/CkF');

CREATE VIEW report 
AS SELECT Id,Path,
SUBSTRING(Path,0,First) AS 'year',
SUBSTRING(Path,First+1,Second-First-1) AS 'month',
SUBSTRING(Path,Second+1,LEN(Path)-Second) AS 'com'
FROM (
SELECT *,
CHARINDEX('/', Path) AS 'First',
LEN(Path) -  CHARINDEX('/', REVERSE(Path))+1 AS 'Second'
FROM TableX) AS M

Demo

回應 2
分享
檢舉

2015evanotes iT邦新手 1 級 ‧ 2021-05-21 10:33:48 檢舉

快又漂亮還有DEMO 學習了 :)

rogeryao iT邦超人 8 級 ‧ 2021-06-18 15:22:09 檢舉

CREATE VIEW report 
AS SELECT Id,Path,
d.vals.value('(/TR/TD)[1]','VARCHAR(10)') AS 'year',
d.vals.value('(/TR/TD)[2]','VARCHAR(10)') AS 'month',
d.vals.value('(/TR/TD)[3]','VARCHAR(10)') AS 'com'
FROM (
SELECT Id,Path,
CAST('<TR><TD>' + REPLACE(Path, '/', '</TD><TD>') + '</TD></TR>' AS xml) vals
FROM TableX) AS d

Demo

登入發表回應

allenlwh

iT邦高手 1 級 ‧ 2021-05-21 09:48:54

DECLARE @TmpTable TABLE 
(
	id int,
	path varchar(20)
)

insert into @TmpTable values (1,'2001/1/A')
insert into @TmpTable values (2,'2001/2/B')
insert into @TmpTable values (3,'2001/1/C')

  select id,SUBSTRING(path,0,CHARINDEX('/',path)) as [year]
		,replace(SUBSTRING(path,5,(len(path)-CHARINDEX('/',REVERSE(path))+1)-(CHARINDEX('/',path))),'/','') as [month]
		,replace(SUBSTRING(path,(len(path)-CHARINDEX('/',REVERSE(path))+1),CHARINDEX('/',path)),'/','') as [com]
	from @TmpTable

驗證查詢結果

建立一個 VIEW 叫 report

CREATE VIEW [dbo].[report]
AS
  select id,SUBSTRING(path,0,CHARINDEX('/',path)) as [year]
		,replace(SUBSTRING(path,5,(len(path)-CHARINDEX('/',REVERSE(path))+1)-(CHARINDEX('/',path))),'/','') as [month]
		,replace(SUBSTRING(path,(len(path)-CHARINDEX('/',REVERSE(path))+1),CHARINDEX('/',path)),'/','') as [com]
	from @yourTable
GO