上周杂事较多,先找到替代方法后就没再管了,今天抽空把资料简洁一下,再放上来.
一个table,先写了个view,然后再写个view查前面的view,用于条件判断.
因为原来写view的早就不在了,所以也不知道为什么要这样写.
tb001:
empno,COMPANY_CODE,EMAIL,MOBILE_PHONE
Y15036,02,Y15036@domain.com,
Y05043,02,Y05043@domain.com,
Y11055,02,Y11055@domain.com,
Y11104,02,Y11104@domain.com,99Y11104
Y02131,02,Y02131@domain.com,99Y02131
Y04001,02,Y04001@domain.com,99Y04001
Y04061,02,Y04061@domain.com,99Y04061
Y05039,02,Y05039@domain.com,99Y05039
Y05110,02,Y05110@domain.com,99Y05110
Y13029,02,Y13029@domain.com,99Y13029
Y13008,02,Y13008@domain.com,99Y13008
Y13043,02,Y13043@domain.com,99Y13043
Y13052,02,Y13052@domain.com,99Y13052
view1:
ALTER VIEW [dbo].[tb001_view_New]
AS
SELECT EMPNO, MOBILE_PHONE_HR, EMAIL, COMPANY_CODE,
CASE WHEN company_code = 02 THEN '+886' + substring(replace(MOBILE_PHONE_HR, '-', ''), 2, 9) ELSE MOBILE_PHONE_HR END AS MOBILE_PHONE
FROM (SELECT EMPNO, [COMPANY_CODE], CASE WHEN MOBILE_PHONE = '' THEN NULL
ELSE MOBILE_PHONE END AS MOBILE_PHONE_HR,
CASE WHEN EMAIL = 'admin@domain.com' THEN NULL ELSE EMAIL END AS EMAIL
FROM dbo.tb001
) AS t
WHERE (MOBILE_PHONE_HR IS NOT NULL) OR
(EMAIL IS NOT NULL)
view2:
ALTER VIEW [dbo].[tb001_view]
AS
SELECT EMPNO, MOBILE_PHONE
FROM dbo.tb001_view_New
WHERE (EMPNO NOT IN
(SELECT EMPNO
FROM tb001_view_new AS tb001_view_new_4
WHERE (MOBILE_PHONE IN
(SELECT MOBILE_PHONE
FROM dbo.tb001_view_new AS tb001_view_new_3
WHERE (MOBILE_PHONE IS NOT NULL)
GROUP BY MOBILE_PHONE
HAVING (COUNT(1) > 1)))
UNION
SELECT EMPNO
FROM dbo.tb001_view_new AS tb001_view_new_2
WHERE (EMAIL IN
(SELECT EMAIL
FROM dbo.tb001_view_new AS tb001_view_new_1
WHERE (EMAIL IS NOT NULL)
GROUP BY EMAIL
HAVING (COUNT(1) > 1)))))
除了PHP中, SELECT count(*) FROM [bonne].[dbo].[tb001_view] 会得到全部13笔
在PHP中, SELECT count(*) FROM [bonne].[dbo].[tb001_view] 只会得到MOBILE_PHONE不为空的10笔.
<?PHP
$con=mssql_connect("c10133013","sa","Ab-123456");
$db=mssql_select_db("bonne",$con);
$query = " select count(*) from tb001_view ";
$results= mssql_query($query);
while ($row = mssql_fetch_array($results)){
echo '->'.$row[0].PHP_EOL;
}
[root@ksyoweb01 tmp]# php tt.php
->10
大神来帮忙解惑了~~
