从数据库中的不同表中获取信息

Question

我有两张桌子：

       User
---------------------
id        primary key 
name      varchar 
mobile    varchar

示例数据：(1, airtel, '9887456321,6985326598,88523695874')

         Client
---------------------------
id              primary key
clientname      varchar 
mobileno        varchar 

示例数据：(1,John Doe, 9887456321)

我希望根据移动字段值从第一个表中搜索客户端名称。

如果移动字段有值(9887456321,6985326598,88523695874)，那么我如何知道该移动电话号码属于哪个客户端名称？

Answer 1

在client表和另一个表之间必须有一个匹配的列。从你的描述中还不清楚那列是什么。从您所说的来看，我相信您的模式如下所示：

Create Table MobileNumbers
(
    Id ... not null Primary Key
    , Name varchar(??) not null
    , Mobile varchar(??) not null
)

Create Table Client
(
    Id ... not null Primary Key
    , ClientName varchar(??) not null
    , MobileNo varchar(??) not null
)

假设Client.Mobile与MobileNumbers.Name匹配，我们有：

Select ...
From Client
    Join MobileNumbers
        On MobileNumbers.MobileNo = Client.Mobile
Where Client.Mobile In('9887456321','6985326598','88523695874')

在这个结构中，我假设每个Mobile值包含一个数字，而不是以逗号分隔的数字列表。如果实际情况是单个单元格可以包含多个值(请编辑您的原始帖子就是这样)，那么正如Martin所提到的，您需要将数据规范化。

编辑

考虑到您对OP的修订，根本问题是在一列中有多个值。正确的解决办法是将移动号码标准化为第二个表格：

Create Table UserMobileNumber
(
    UserId ... not null References User ( Id )
    , Carrier varchar(??) not null
    , Mobile varchar(??) not null
    , Constraint UC_UserMobileNumber Unique ( Mobile )
)

然后解决方案变得琐碎：

Select ...
From User
    Join UserMobileNumber
        On UserMobileNumber.UserId = User.Id
Where UserMobileNumber.Mobile In('9887456321','6985326598','88523695874')

但是，在过渡期间，您需要的是一个拆分函数：

Create Function dbo.udf_Split
(   
    @DelimitedList nvarchar(max)
    , @Delimiter nvarchar(2) = ','
)
Returns Table
As
Return
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            As List
            , Len(@Delimiter) As DelimiterLen
        )
        , Numbers As 
        (
        Select TOP (Len(@DelimitedList) + 2) Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.columns As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                           
                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) 
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value < Len(CL.List)
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
    )

现在你可以做这样的事情：

Select ...
From User
    Outer Apply dbo.Split( User.Mobile ) As M
    Join Client
        On Client.MobileNo = M.Value
Where M.Value In('9887456321','6985326598','88523695874')