DISTINCT into SQL 语句不能很好地工作

Question

下面的代码可以正常工作，解决一个问题，该问题显示许多记录，而它只显示一个记录。（例如：如果用户从 UserInfo 表中有 10 个广告进入广告表，其中 5 个广告的值类似于查询字符串结果值，那么他重复用户 5 次，假设出现一次，无论他有广告在哪里 Wtag 列。

  if (Request.QueryString["Searchfor"] != null)
                        {
                            using (SqlConnection srcbizhsql = new SqlConnection(sc))
                            {
                                srcbizhsql.Open();
                                SqlDataAdapter DAsearchbiz = new SqlDataAdapter(@"SELECT DISTINCT UI.[UID],UI.[Country],UI.[State],UI.[City],UI.[Logo],UI.[Website],UI.[UsrType],UI.[BizCateg],UI.[BizSubCateg],UI.[Twitter],UI.[GooglePlus],UI.[Facebook],UI.[CompNme],UI.[RegDate] ,SUBSTRING([CompDesc],1,40) AS CompDesc  ,AD.[Wtags] FROM UserInfo AS UI , ads AS AD WHERE UI.UID = AD.UID and UI.[Country]= @Location AND UI.[UsrType]= 'Business' AND CHARINDEX(@Wtag, AD.[Wtags] )>0 ORDER BY UI.[RegDate] DESC", sc);
                                DataSet DSsrchBiz = new DataSet();
                                DAsearchbiz.SelectCommand.Parameters.AddWithValue("@Location", cookie.Value);
                                DAsearchbiz.SelectCommand.Parameters.AddWithValue("@Wtag", result);
                                DAsearchbiz.Fill(DSsrchBiz);
                                SrchbizHomLstviw.DataSource = DSsrchBiz.Tables[0];
                                SrchbizHomLstviw.DataBind();
                                SrchMultiView.ActiveViewIndex = 1;
                                RebindAds();
                                RebindJobs();
                            }
                        }

Answer 1

感谢您在多次尝试后已修复

，如下所示

SELECT DISTINCT UI.[UID] ,UI.[Country] ,UI.[State] ,UI.[City] ,UI.[Logo] ,UI.[Website] ,UI.[UsrType] ,SUBSTRING(UI.[CompDesc], 1, 60) AS CompDesc ,UI.[BizCateg] ,UI.[BizSubCateg] ,UI.[Twitter] ,UI.[GooglePlus] ,UI.[Facebook] ,UI.[CompNme] ,UI.[RegDate] FROM UserInfo AS UI JOIN (SELECT DISTINCT ads.[UID] FROM ads WHERE CHARINDEX(@Wtag, ads.[Wtags])> 0) AS AD ON AD.[UID] = UI.[UID] WHERE UI.[Country] = @Location AND UI.[UsrType] = 'Business' ORDER BY UI.[RegDate] DESC

Answer 2

您确实应该开始使用 ANSI-92 样式连接，而不是旧的 ANSI-89 样式。该语法已经存在了 20 多年。踢的坏习惯：使用旧式的JOINs。

下面是包含"新式"联接和一些格式的查询。

SELECT DISTINCT UI.[UID]
    , UI.[Country]
    , UI.[State]
    , UI.[City]
    , UI.[Logo]
    , UI.[Website]
    , UI.[UsrType]
    , UI.[BizCateg]
    , UI.[BizSubCateg]
    , UI.[Twitter]
    , UI.[GooglePlus]
    , UI.[Facebook]
    , UI.[CompNme]
    , UI.[RegDate] 
    , SUBSTRING([CompDesc], 1, 40) AS CompDesc  
    , AD.[Wtags] 
FROM UserInfo AS UI
JOIN ads AS AD ON UI.UID = AD.UID 
WHERE UI.[Country] = @Location 
    AND UI.[UsrType] = 'Business' 
    AND CHARINDEX(@Wtag, AD.[Wtags])> 0 
ORDER BY UI.[RegDate] DESC

然而，我不明白的是问题是什么。