iT邦幫忙

0

SQL Search

我有1個databases 如下:
https://ithelp.ithome.com.tw/upload/images/20211010/20125845qL4xpnQC3x.jpg

我需要在新增資料時檢查 Tel1 / Tel2 / Tel3 是否在我的 databases 內出現過.

謝謝幫忙

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
0
純真的人
iT邦大師 1 級 ‧ 2021-10-10 18:08:56
最佳解答

是這個吧?
若有沒出現過的資料就新增~

insert into databases(
	[Order_No]
	,[Customer Name]
	,Tel1
	,Tel2
	,Tel3
)
select '' as [Order_No]
,'' as [Customer Name]
,'' as Tel1
,'' as Tel2
,'' as Tel3
where not exists(
	select 0
	from databases
	where Tel1 = ''
	or Tel2 = ''
	or Tel3 = ''
)
1
rogeryao
iT邦大師 1 級 ‧ 2021-10-10 12:00:27
CREATE TABLE [XX](
OrderNO NVARCHAR(20) NULL,
CustomerName NVARCHAR(20) NULL,
Tel1 NVARCHAR(20) NULL,
Tel2 NVARCHAR(20) NULL,
Tel3 NVARCHAR(20) NULL
);
INSERT INTO XX (OrderNO,CustomerName,Tel1,Tel2,Tel3) 
VALUES
('EGS000001','j','88','22','33'),
('EGS000002','p','99','88','66'),
('EGS000003','m','45','22','55'),
('EGS000004','j','91','65','88'),
('EGS000001','n','45','66','22');
DECLARE @INPUT NVARCHAR(20)
SET @INPUT='22'
SELECT ISNULL(SUM(CASE WHEN TRIM(Tel1)=TRIM(@INPUT) THEN 1 ELSE 0 END +
CASE WHEN TRIM(Tel2)=TRIM(@INPUT) THEN 1 ELSE 0 END +
CASE WHEN TRIM(Tel3)=TRIM(@INPUT) THEN 1 ELSE 0 END),0) AS NUM
FROM XX
WHERE TRIM(Tel1)=TRIM(@INPUT) OR TRIM(Tel2)=TRIM(@INPUT) OR TRIM(Tel3)=TRIM(@INPUT)

Demo

我要發表回答

立即登入回答