34,587
社区成员
发帖
与我相关
我的任务
分享
SELECT DISTINCT 所需的字段名
FROM B_SR_CATEGORY AS B_SR_CATEGORY_2 LEFT OUTER JOIN
B_SR_CATEGORY ON B_SR_CATEGORY_2.parent_category = B_SR_CATEGORY.id RIGHT OUTER JOIN
B_SR_CATEGORY AS B_SR_CATEGORY_1 ON
B_SR_CATEGORY_2.id = B_SR_CATEGORY_1.parent_category RIGHT OUTER JOIN
B_SR_CATEGORY AS B_SR_CATEGORY_3 ON
B_SR_CATEGORY_1.id = B_SR_CATEGORY_3.parent_category RIGHT OUTER JOIN
B_SR_STATUS RIGHT OUTER JOIN
B_SR ON B_SR_STATUS.id = B_SR.sr_status LEFT OUTER JOIN
B_CONTACT ON B_SR.created_by = B_CONTACT.id LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL ON B_SR.id = V_ACT_WITHOUTCANCEL.sr AND
V_ACT_WITHOUTCANCEL.activity_type = 'c70ce7b6-ffb1-4b24-a769-7d81c0d344da' AND
V_ACT_WITHOUTCANCEL.create_date =
(SELECT MAX(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = 'c70ce7b6-ffb1-4b24-a769-7d81c0d344da')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_13 ON B_SR.id = V_ACT_WITHOUTCANCEL_13.sr AND
(V_ACT_WITHOUTCANCEL_13.activity_type = '8206732c-ddfc-441b-9e82-2eefcec9413e' OR
V_ACT_WITHOUTCANCEL_13.activity_type = 'a4c71a95-8de8-4a6d-9ae0-6bf2e0173809') AND
V_ACT_WITHOUTCANCEL_13.create_date =
(SELECT MAX(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = '8206732c-ddfc-441b-9e82-2eefcec9413e' OR
activity_type = 'a4c71a95-8de8-4a6d-9ae0-6bf2e0173809')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_12 ON B_SR.id = V_ACT_WITHOUTCANCEL_12.sr AND
V_ACT_WITHOUTCANCEL_12.activity_type = 'CC1ADA3D-441B-4FCF-92CE-33A79B48B688' AND
V_ACT_WITHOUTCANCEL_12.create_date =
(SELECT MIN(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = 'CC1ADA3D-441B-4FCF-92CE-33A79B48B688') AND
(dbo.SplitOper(description, 2) IN
(SELECT name_cn
FROM B_CONTACT
WHERE (id IN
(SELECT contact
FROM B_REL_GRP_CTC
WHERE (group_ = 'cb0043a0-8cc2-4e79-8c2d-276f60d9c277')))))) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_11 ON B_SR.id = V_ACT_WITHOUTCANCEL_11.sr AND
V_ACT_WITHOUTCANCEL_11.activity_type = '0d5bb539-3956-4e6d-882a-92c4070b24f6' AND
V_ACT_WITHOUTCANCEL_11.create_date =
(SELECT MAX(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = '0d5bb539-3956-4e6d-882a-92c4070b24f6')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_10 ON B_SR.id = V_ACT_WITHOUTCANCEL_10.sr AND
V_ACT_WITHOUTCANCEL_10.activity_type = '112f9c77-0707-4d19-9c43-8e7bca64b480' AND
V_ACT_WITHOUTCANCEL_10.create_date =
(SELECT MIN(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = '112f9c77-0707-4d19-9c43-8e7bca64b480')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_9 ON B_SR.id = V_ACT_WITHOUTCANCEL_9.sr AND
V_ACT_WITHOUTCANCEL_9.activity_type = 'cd5cdf1c-ad13-427f-9ff2-b9f0a199ded9' AND
V_ACT_WITHOUTCANCEL_9.create_date =
(SELECT MAX(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = 'cd5cdf1c-ad13-427f-9ff2-b9f0a199ded9')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_8 ON B_SR.id = V_ACT_WITHOUTCANCEL_8.sr AND
V_ACT_WITHOUTCANCEL_8.activity_type = 'c9c7d75b-d1e2-4036-9d69-a4967037a735' AND
V_ACT_WITHOUTCANCEL_8.create_date =
(SELECT MIN(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = 'c9c7d75b-d1e2-4036-9d69-a4967037a735')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_7 ON B_SR.id = V_ACT_WITHOUTCANCEL_7.sr AND
V_ACT_WITHOUTCANCEL_7.activity_type = '518d615d-2bbb-40fd-952a-28943ff5690a' AND
V_ACT_WITHOUTCANCEL_7.create_date =
(SELECT MAX(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = '518d615d-2bbb-40fd-952a-28943ff5690a')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_6 ON B_SR.id = V_ACT_WITHOUTCANCEL_6.sr AND
V_ACT_WITHOUTCANCEL_6.activity_type = '0fe96b5f-6ff0-4aca-a38c-3472f379fe72' AND
V_ACT_WITHOUTCANCEL_6.create_date =
(SELECT MIN(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = '0fe96b5f-6ff0-4aca-a38c-3472f379fe72')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_5 LEFT OUTER JOIN
B_CONTACT AS B_CONTACT_3 ON V_ACT_WITHOUTCANCEL_5.person = B_CONTACT_3.id ON
B_SR.id = V_ACT_WITHOUTCANCEL_5.sr AND
V_ACT_WITHOUTCANCEL_5.activity_type = '123fec6c-a23f-45af-821c-73a69ba72df4' AND
V_ACT_WITHOUTCANCEL_5.create_date =
(SELECT MIN(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = '123fec6c-a23f-45af-821c-73a69ba72df4')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_4 ON B_SR.id = V_ACT_WITHOUTCANCEL_4.sr AND
V_ACT_WITHOUTCANCEL_4.activity_type = 'b94add57-b91a-4969-b778-9b094165b313' AND
V_ACT_WITHOUTCANCEL_4.create_date =
(SELECT MIN(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = 'b94add57-b91a-4969-b778-9b094165b313')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_3 ON B_SR.id = V_ACT_WITHOUTCANCEL_3.sr AND
V_ACT_WITHOUTCANCEL_3.activity_type = '1A394E84-5C32-42B3-AB8D-83747304FDA7' AND
V_ACT_WITHOUTCANCEL_3.create_date =
(SELECT MIN(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = '1A394E84-5C32-42B3-AB8D-83747304FDA7')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_2 ON B_SR.id = V_ACT_WITHOUTCANCEL_2.sr AND
V_ACT_WITHOUTCANCEL_2.activity_type = '2c8b5006-2395-4685-81b2-b3808342f28c' AND
V_ACT_WITHOUTCANCEL_2.create_date =
(SELECT MIN(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = '2c8b5006-2395-4685-81b2-b3808342f28c')) LEFT OUTER JOIN
V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_1 ON B_SR.id = V_ACT_WITHOUTCANCEL_1.sr AND
V_ACT_WITHOUTCANCEL_1.activity_type = '483eb40a-5002-42e8-80c1-71b874da58bf' AND
V_ACT_WITHOUTCANCEL_1.create_date =
(SELECT MIN(create_date) AS Expr1
FROM V_ACT_WITHOUTCANCEL
WHERE (sr = B_SR.id) AND (activity_type = '483eb40a-5002-42e8-80c1-71b874da58bf')) LEFT OUTER JOIN
B_SR_SOLUTION ON B_SR.solution = B_SR_SOLUTION.id ON
B_SR_CATEGORY_3.id = B_SR.category LEFT OUTER JOIN
B_ASSET LEFT OUTER JOIN
B_PRODUCT_TYPE ON B_ASSET.product_type = B_PRODUCT_TYPE.id ON B_SR.asset = B_ASSET.id LEFT OUTER JOIN
B_CONTACT_TYPE RIGHT OUTER JOIN
B_ORGANIZATION RIGHT OUTER JOIN
B_CONTACT AS B_CONTACT_2 ON B_ORGANIZATION.id = B_CONTACT_2.organization ON
B_CONTACT_TYPE.id = B_CONTACT_2.type ON B_SR.effective = B_CONTACT_2.id LEFT OUTER JOIN
B_CONTACT AS B_CONTACT_1 ON B_SR.call_user = B_CONTACT_1.id
WHERE (B_SR.project = '6e71994d-0b1d-418a-a9a4-fffda1d0a341') AND (B_SR.create_date >= CONVERT(DATETIME,
'2011-04-19 00:00:00', 102)) AND (B_SR.create_date BETWEEN @begindate AND @enddate)
--随便找了一段,举着例子.
LEFT OUTER JOIN V_ACT_WITHOUTCANCEL AS V_ACT_WITHOUTCANCEL_2
ON B_SR.id = V_ACT_WITHOUTCANCEL_2.sr
AND V_ACT_WITHOUTCANCEL_2.activity_type = '2c8b5006-2395-4685-81b2-b3808342f28c'
AND V_ACT_WITHOUTCANCEL_2.create_date = (
SELECT
MIN(create_date) AS Expr1
FROM
V_ACT_WITHOUTCANCEL
WHERE
(sr = B_SR.id)
AND (activity_type = '2c8b5006-2395-4685-81b2-b3808342f28c')
)
--用outer apply
outer apply
(select top(1) * from V_ACT_WITHOUTCANCEL where activity_type = '2c8b5006-2395-4685-81b2-b3808342f28c' and sr=B_SR.id order by create_date) V_ACT_WITHOUTCANCEL_2