多个sql上，不能让它工作

edqdpe6u 于 2021-06-21 发布在 Mysql

关注(0)|答案(4)|浏览(284)

我正在试图找到一种方法，只显示基于dpa.value的联系人详细信息，因此可以根据dpa值将联系人排除在输出列表之外，如果dpa值等于5，则不包括联系人。如果dpa值=3或4，则排除电子邮件地址。如果值=2或4，则排除电话
排除联系如果db.organizationpersonnels.employedtodate）为空此代码似乎不工作，任何帮助将不胜感激。

SELECT
        DB.OrganisationPersonnel.OrganisationID
      , DB.OrganisationPersonnel.PersonnelID AS [if not ProductPAID =5]
      , DB.Title.Name
      , DB.Person.FirstName
      , DB.Person.MiddleName
      , DB.Person.LastName
      , DB.OrganisationPersonnel.ActualJobTitle
      , DB.Phone.Number AS [if ProductPAID <3]
      , DB.Email.Email AS [if ProductPAID =2 or 5]
      , DB.Person.ProductDPAID
      , DB.ProductDPA.Name
    FROM
       (((DB.OrganisationPersonnel 
    INNER JOIN
       (DB.Person 
    INNER JOIN
       DB.Title 
    ON
       DB.Person.TitleID = DB.Title.ID) 
    ON
       DB.OrganisationPersonnel.PersonnelID = DB.Person.ID) 
    LEFT JOIN
       DB.Phone 
    ON
       DB.OrganisationPersonnel.PhoneID = DB.Phone.ID) 

    LEFT JOIN
       DB.Email 
    ON
       DB.OrganisationPersonnel.EmailID = DB.Email.ID)  

    INNER JOIN
       DB.ProductDPA 
    ON
       DB.Person.ProductDPAID = DB.ProductDPA.ID
    WHERE
       (((DB.OrganisationPersonnel.EmployedToDate) Is Null))
    ORDER BY
       DB.OrganisationPersonnel.OrganisationID;

我试图增加额外的地方，但无法解决如何没有错误。任何帮助将不胜感激，因为我是一个新的sql。

sql mysql

来源：https://stackoverflow.com/questions/49938199/multiple-on-where-sql-cant-get-it-to-work

4条答案

按热度按时间

2ekbmq321#

您可以编写简单的查询，但长度如下

--[PersonnelID is null if ProductDPA =5]
SELECT 
    dbo.OrganisationPersonnel.OrganisationID
  , null AS PersonnelID -- PersonnelID is null if ProductDPA =5
  , dbo.Title.Name
  , dbo.Person.FirstName
  , dbo.Person.MiddleName
  , dbo.Person.LastName
  , dbo.OrganisationPersonnel.ActualJobTitle
  , dbo.Phone.Number 
  , dbo.Email.Email 
  , dbo.Person.ProductDPAID
  , dbo.ProductDPA.Name
FROM
   dbo.OrganisationPersonnel  
   INNER JOIN dbo.Person ON dbo.OrganisationPersonnel.PersonnelID = dbo.Person.ID
   INNER JOIN dbo.Title ON dbo.Person.TitleID = dbo.Title.ID
   LEFT JOIN  dbo.Phone ON dbo.OrganisationPersonnel.PhoneID = dbo.Phone.ID
   LEFT JOIN  dbo.Email ON dbo.OrganisationPersonnel.EmailID = dbo.Email.ID
   INNER JOIN dbo.ProductDPA  ON dbo.Person.ProductDPAID = dbo.ProductDPA.ID
WHERE dbo.Person.ProductDPAID = 5  --All ProductDPA =5 & All EmployedToDate Is Null
or dbo.OrganisationPersonnel.EmployedToDate Is Null 
union all  
--Phone Number is null if ProductDPA = 2  
SELECT
    dbo.OrganisationPersonnel.OrganisationID
  , OrganisationPersonnel.PersonnelID 
  , dbo.Title.Name
  , dbo.Person.FirstName
  , dbo.Person.MiddleName
  , dbo.Person.LastName
  , dbo.OrganisationPersonnel.ActualJobTitle
  , null as Number  --Phone Number is null if ProductDPA = 2  
  , dbo.Email.Email 
  , dbo.Person.ProductDPAID
  , dbo.ProductDPA.Name
FROM
   dbo.OrganisationPersonnel  
   INNER JOIN dbo.Person ON dbo.OrganisationPersonnel.PersonnelID = dbo.Person.ID
   INNER JOIN dbo.Title ON dbo.Person.TitleID = dbo.Title.ID
   LEFT JOIN  dbo.Phone ON dbo.OrganisationPersonnel.PhoneID = dbo.Phone.ID
   LEFT JOIN  dbo.Email ON dbo.OrganisationPersonnel.EmailID = dbo.Email.ID
   INNER JOIN dbo.ProductDPA  ON dbo.Person.ProductDPAID = dbo.ProductDPA.ID
WHERE ProductDPAID in (2)  --Phone Number is null if ProductDPA = 2 
union all
--Email is null if ProductDPA =3
SELECT
    dbo.OrganisationPersonnel.OrganisationID
  , OrganisationPersonnel.PersonnelID  
  , dbo.Title.Name
  , dbo.Person.FirstName
  , dbo.Person.MiddleName
  , dbo.Person.LastName
  , dbo.OrganisationPersonnel.ActualJobTitle
  , dbo.Phone.Number   
  , null AS Email --Email is null if ProductDPA =3 
  , dbo.Person.ProductDPAID
  , dbo.ProductDPA.Name
FROM
   dbo.OrganisationPersonnel  
   INNER JOIN dbo.Person ON dbo.OrganisationPersonnel.PersonnelID = dbo.Person.ID
   INNER JOIN dbo.Title ON dbo.Person.TitleID = dbo.Title.ID
   LEFT JOIN  dbo.Phone ON dbo.OrganisationPersonnel.PhoneID = dbo.Phone.ID
   LEFT JOIN  dbo.Email ON dbo.OrganisationPersonnel.EmailID = dbo.Email.ID
   INNER JOIN dbo.ProductDPA  ON dbo.Person.ProductDPAID = dbo.ProductDPA.ID
WHERE ProductDPAID in (3)  --Email is null if ProductDPA =3 
union all
--Phone Number and Email is null if ProductDPA = 4
SELECT
    dbo.OrganisationPersonnel.OrganisationID
  , OrganisationPersonnel.PersonnelID  
  , dbo.Title.Name
  , dbo.Person.FirstName
  , dbo.Person.MiddleName
  , dbo.Person.LastName
  , dbo.OrganisationPersonnel.ActualJobTitle
  , null as Number     
  , null AS Email  
  , dbo.Person.ProductDPAID
  , dbo.ProductDPA.Name
FROM
   dbo.OrganisationPersonnel  
   INNER JOIN dbo.Person ON dbo.OrganisationPersonnel.PersonnelID = dbo.Person.ID
   INNER JOIN dbo.Title ON dbo.Person.TitleID = dbo.Title.ID
   LEFT JOIN  dbo.Phone ON dbo.OrganisationPersonnel.PhoneID = dbo.Phone.ID
   LEFT JOIN  dbo.Email ON dbo.OrganisationPersonnel.EmailID = dbo.Email.ID
   INNER JOIN dbo.ProductDPA  ON dbo.Person.ProductDPAID = dbo.ProductDPA.ID
WHERE ProductDPAID in (4)  --Email is null if ProductDPA = 4 
ORDER BY
   dbo.OrganisationPersonnel.OrganisationID;

您必须编写join，如示例所示

赞(0）回复(0）举报 2021-06-21

5jvtdoz22#

请尝试下面的查询。

SELECT op.OrganisationID
    , (CASE WHEN ProductPAID = 5 THEN op.PersonnelID ELSE NULL END) AS [if not ProductPAID =5]
    , t.Name
    , p.FirstName
    , p.MiddleName
    , p.LastName
    , op.ActualJobTitle
    , (CASE WHEN ProductPAID < 3 THEN ph.Number ELSE NULL END) AS [if ProductPAID <3]
    , (CASE WHEN ProductPAID = 2 OR ProductPAID = 5 THEN e.Email ELSE NULL END) AS [if ProductPAID =2 or 5]
    , p.ProductDPAID
    , dpa.Name
FROM DB.OrganisationPersonnel op
JOIN DB.Person p ON op.PersonnelID = p.ID
JOIN DB.Title t ON p.TitleID = t.ID
JOIN DB.ProductDPA dpa ON p.ProductDPAID = dpa.ID
LEFT JOIN DB.Email e ON op.EmailID = e.ID
LEFT JOIN DB.Phone ph ON op.PhoneID = op.PhoneID
WHERE op.EmployedToDate IS NULL
ORDER BY op.OrganisationID;

赞(0）回复(0）举报 2021-06-21

pwuypxnk3#

您的连接很好（尽管大多数人更喜欢join后跟on的语法变体）。但您应该开始使用别名来简化源代码的编写和读取：

SELECT
    op.OrganisationID
  , t.Name
  , p.FirstName
...
FROM
   (((DB.OrganisationPersonnel AS op
INNER JOIN
   (DB.Person AS p
INNER JOIN
   DB.Title AS t
ON
   p.TitleID = t.ID) 
ON
   op.PersonnelID = p.ID) 
...
``` `IF` 在标准sql中使用 `CASE` :

SELECT
op.OrganisationID
, CASE WHEN ProductPAID <> 5 THEN op.PersonnelID END AS PersonnelID
, DB.Title.Name
...
, CASE WHEN ProductPAID <3 THEN p.Number END AS PhoneNumber
, CASE WHEN ProductPAID IN (2, 5) THEN e.Email END AS email

对于不匹配的数据，这将返回null，但是有一个可选的 `ELSE` 用默认值覆盖它。

赞(0）回复(0）举报 2021-06-21

js81xvg64#

首先，你不需要任何括号 FROM 条款。把它们取下来。数据库引擎查询优化器将以最好的顺序为您执行连接。
下一步，您的计算机上会出现语法错误 ON 上下文。你应该使用 AND 表达式，因为它只是一个布尔表达式。而不是写作

ON
   DB.Person.TitleID = DB.Title.ID) 
ON
   DB.OrganisationPersonnel.PersonnelID = DB.Person.ID)

一定要写

ON DB.Person.TitleID = DB.Title.ID
   AND DB.OrganisationPersonnel.PersonnelID = DB.Person.ID

赞(0）回复(0）举报 2021-06-21

我来回答

多个sql上，不能让它工作

4条答案

相关问题

热门标签

最新问答