mysql查询不确定的信息,mysql – SQL Join,不确定复杂查询中的确切语法

我正在尝试匹配两位数据,添加到现有查询.

我希望查询本身解释我正在尝试做什么.我遇到麻烦的是LEFT JOIN从业者p ON insolvencies.practitioner = practitioners.id,

SELECT DISTINCT i.id AS id,

i.company AS company,

i.insolvencyDate AS insolvency_date,

i.city AS city,

i.createdOn AS createdOn,

1 AS rank_id,

t.entryCopy AS insolvency_type,

i.businessNature AS business_nature,

p.name AS pract_comp_name,

'I' AS i_type

FROM insolvencies i,

LEFT JOIN practitioners p ON insolvencies.practitioner = practitioners.id,

lookupcopy t

WHERE i.checked = 1

AND t.id = i.insolvencyType

AND i.insolvencyDate >= {ts '2010-01-22 00:00:00'}

AND i.insolvencyDate <= {ts '2011-10-20 00:00:00'}

AND (LOWER(p.name) LIKE '%Hodgsons%')

GROUP BY id

解决方法:

我想你正在寻找这样的东西：

SELECT DISTINCT i.id AS id

, i.company AS company

, i.insolvencyDate AS insolvency_date

, i.city AS city

, i.createdOn AS createdOn

, 1 AS rank_id

, t.entryCopy AS insolvency_type

, i.businessNature AS business_nature

, p.name AS pract_comp_name

, 'I' AS i_type

FROM insolvencies i

LEFT JOIN practitioners p ON insolvencies.practitioner = practitioners.id

CROSS JOIN lookupcopy t

WHERE i.checked = 1

AND t.id = i.insolvencyType

AND i.insolvencyDate >= {ts '2010-01-22 00:00:00'}

AND i.insolvencyDate <= {ts '2011-10-20 00:00:00'}

AND (LOWER(p.name) LIKE '%Hodgsons%')

GROUP BY id

表之间的内容与CROSS JOIN相同.

但无论如何,您应该阅读有关交叉连接,内部连接和外部连接的信息,并使用最适合您的情况.

标签：sql,sql-server,mysql

来源： https://codeday.me/bug/20190726/1541492.html