注册    登录
私信  •  关注

Raymond Nijland
Raymond Nijland 最近创建的主题
» Raymond Nijland 创建的更多主题
Raymond Nijland 最近回复了
6 年前
回复了 Raymond Nijland 创建的主题 » regex模式不适用于mysql regexp

不使用regex的困难解决方法,这篇文章只是为了表明这是可能的。
诀窍是制作一个mysql数字生成器并使用嵌套 SUBSTRING_INDEX() 函数将字符串切割为标记。

查询 

 SELECT 
   separated_key_values.query
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           separated_key_values.separated_property
         , '='
         , 1
       )
      ,'='
     , -1
   ) AS property_key
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           separated_key_values.separated_property
         , '='
         , 2
       )
      ,'='
     , -1
   ) AS property_value   
FROM (

SELECT 
  DISTINCT
     search.query 
   , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search.query
         , '&'
         , number_generator.row_number
       )
      ,'&'
     , -1
   ) separated_property
FROM (
  SELECT 
   @row := @row + 1 AS row_number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) AS init_user_params
) AS number_generator
CROSS JOIN 
 search 

) AS separated_key_values
ORDER BY 
 separated_key_values.query ASC

结果 

| query                                      | property_key | property_value |
| ------------------------------------------ | ------------ | -------------- |
| city=1&propertyType=HOUSE&serviceType=SALE | city         | 1              |
| city=1&propertyType=HOUSE&serviceType=SALE | serviceType  | SALE           |
| city=1&propertyType=HOUSE&serviceType=SALE | propertyType | HOUSE          |
| city=1&serviceType=SALE&propertyType=HOUSE | city         | 1              |
| city=1&serviceType=SALE&propertyType=HOUSE | propertyType | HOUSE          |
| city=1&serviceType=SALE&propertyType=HOUSE | serviceType  | SALE           |
| city=2&propertyType=HOUSE&serviceType=SALE | serviceType  | SALE           |
| city=2&propertyType=HOUSE&serviceType=SALE | city         | 2              |
| city=2&propertyType=HOUSE&serviceType=SALE | propertyType | HOUSE          |
| propertyType=HOUSE&city=2&serviceType=SALE | city         | 2              |
| propertyType=HOUSE&city=2&serviceType=SALE | serviceType  | SALE           |
| propertyType=HOUSE&city=2&serviceType=SALE | propertyType | HOUSE          |
| propertyType=HOUSE&serviceType=SALE&city=1 | city         | 1              |
| propertyType=HOUSE&serviceType=SALE&city=1 | serviceType  | SALE           |
| propertyType=HOUSE&serviceType=SALE&city=1 | propertyType | HOUSE          |
| serviceType=RENTAL                         | serviceType  | RENTAL         |
| serviceType=RENTAL&propertyType=HOUSE      | propertyType | HOUSE          |
| serviceType=RENTAL&propertyType=HOUSE      | serviceType  | RENTAL         |
| serviceType=SALE                           | serviceType  | SALE           |
| serviceType=SALE&propertyType=FARM&city=1  | serviceType  | SALE           |
| serviceType=SALE&propertyType=FARM&city=1  | propertyType | FARM           |
| serviceType=SALE&propertyType=FARM&city=1  | city         | 1              |
| serviceType=SALE&propertyType=HOUSE        | propertyType | HOUSE          |
| serviceType=SALE&propertyType=HOUSE        | serviceType  | SALE           |
| serviceType=SALE&propertyType=HOUSE&city=1 | city         | 1              |
| serviceType=SALE&propertyType=HOUSE&city=1 | propertyType | HOUSE          |
| serviceType=SALE&propertyType=HOUSE&city=1 | serviceType  | SALE           |
| serviceType=SALE&propertyType=HOUSE&city=2 | propertyType | HOUSE          |
| serviceType=SALE&propertyType=HOUSE&city=2 | city         | 2              |
| serviceType=SALE&propertyType=HOUSE&city=2 | serviceType  | SALE           |
| serviceType=SALE&propertyType=UNIT         | propertyType | UNIT           |
| serviceType=SALE&propertyType=UNIT         | serviceType  | SALE           |

看见 demo

在那之后,它就像添加条件聚合一样简单。

查询 

SELECT 
  separated_key_values.query

, 

 (
   SUM(separated_key_values.property_key = 'serviceType') > 0
 AND
   SUM(separated_key_values.property_value = 'SALE') > 0

 ) AS has_serviceType_SALE

, 

 (
   SUM(separated_key_values.property_key = 'propertyType') > 0
 AND
   SUM(separated_key_values.property_value = 'HOUSE') > 0

 ) AS has_propertyType_HOUSE

, 

 (
   SUM(separated_key_values.property_key = 'City') > 0
 AND
   SUM(separated_key_values.property_value = '1') > 0

 )  AS has_City_1

, (

 (
   SUM(separated_key_values.property_key = 'serviceType') > 0
 AND
   SUM(separated_key_values.property_value = 'SALE') > 0

 ) 

 + 

 (
   SUM(separated_key_values.property_key = 'propertyType') > 0
 AND
   SUM(separated_key_values.property_value = 'HOUSE') > 0

 ) 

 + 

 (
   SUM(separated_key_values.property_key = 'City') > 0
 AND
   SUM(separated_key_values.property_value = '1') > 0

 )   

  ) AS has_mask 

, COUNT(*)

FROM (
SELECT 
   search_alias.query
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search_alias.separated_property
         , '='
         , 1
       )
      ,'='
     , -1
   ) AS property_key
 , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search_alias.separated_property
         , '='
         , 2
       )
      ,'='
     , -1
   ) AS property_value   
FROM (

SELECT 
  DISTINCT
     search.query 
   , SUBSTRING_INDEX(
       SUBSTRING_INDEX(
           search.query
         , '&'
         , number_generator.row_number
       )
      ,'&'
     , -1
   ) separated_property
FROM (
  SELECT 
   @row := @row + 1 AS row_number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) AS init_user_params
) AS number_generator
CROSS JOIN 
 search 

) AS search_alias
) AS separated_key_values
GROUP BY 
 separated_key_values.query
HAVING 
 has_mask = COUNT(*)

结果 

| query                                      | has_serviceType_SALE | has_propertyType_HOUSE | has_City_1 | has_mask | COUNT(*) |
| ------------------------------------------ | -------------------- | ---------------------- | ---------- | -------- | -------- |
| city=1&propertyType=HOUSE&serviceType=SALE | 1                    | 1                      | 1          | 3        | 3        |
| city=1&serviceType=SALE&propertyType=HOUSE | 1                    | 1                      | 1          | 3        | 3        |
| propertyType=HOUSE&serviceType=SALE&city=1 | 1                    | 1                      | 1          | 3        | 3        |
| serviceType=SALE                           | 1                    | 0                      | 0          | 1        | 1        |
| serviceType=SALE&propertyType=HOUSE        | 1                    | 1                      | 0          | 2        | 2        |
| serviceType=SALE&propertyType=HOUSE&city=1 | 1                    | 1                      | 1          | 3        | 3        |

看见 demo

您还可以将列输出放入 HAVING 子句,以便不输出这些列。
见 demo

注释
这不会在大型表上扩展,很可能regex查询也不会扩展,因为索引很可能无法使用。

一种解决方法可能是使用具有正确索引的临时表,并使用第一个查询对索引的临时表进行前缀填充和条件聚合。

6 年前
回复了 Raymond Nijland 创建的主题 » 准备语句循环行mysql

您要将列转换为记录结束计数的接缝 null 或 not null 在那个专栏里。
在大多数数据库系统中,将列转换为记录的过程称为unpivot UNPIVOT() 但是mysql不支持这个。
所以通常情况下 UNION 结合一些聚合函数,如 MAX() , MIX() , SUM() 和 COUNT() 和A CASE END 要模拟的子句 unPIVoT() 关于MySQL。

查询 

SELECT 
   'Id1' AS Col_name
 , SUM(CASE WHEN Table1.Id1 IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Id1 IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Name1' AS Col_name
 , SUM(CASE WHEN Table1.Name1 IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Name1 IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Date1' AS Col_name
 , SUM(CASE WHEN Table1.Date1 IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Date1 IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Process' AS Col_name
 , SUM(CASE WHEN Table1.Process IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Process IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Time' AS Col_name
 , SUM(CASE WHEN Table1.Time IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Time IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

UNION ALL

SELECT 
  'Class' AS Col_name
 , SUM(CASE WHEN Table1.Class IS NULL THEN 1 ELSE 0 END) AS `#Null`
 , SUM(CASE WHEN Table1.Class IS NOT NULL THEN 1 ELSE 0 END) AS `#notNull` 
FROM 
 Table1

结果 

| Col_name | #Null | #notNull |
| -------- | ----- | -------- |
| Id1      | 1     | 2        |
| Name1    | 0     | 3        |
| Date1    | 1     | 2        |
| Process  | 0     | 3        |
| Time     | 3     | 0        |
| Class    | 1     | 2        |

demo

7 年前
回复了 Raymond Nijland 创建的主题 » 有没有更快更好的方法来执行这个mysql查询?

有更多的方法…

左连接方法

查询 

SELECT
    Item.id
  , Item.name
  , Storage.stuff 
FROM
  Item 
LEFT JOIN
  Storage
ON
  Storage.item_id = Item.id 
WHERE
  Storage.stuff IS NULL

不存在方法

查询 

SELECT
   Item.id
 , Item.name
 , NULL AS stuff
FROM 
 Item
WHERE
 NOT EXISTS (
  SELECT 
   1
  FROM 
   Storage
  WHERE 
   Storage.item_id = Item.id
)

不在方法中

查询 

SELECT
   Item.id
 , Item.name
 , NULL AS stuff
FROM 
 Item
WHERE
 Item.id NOT IN (
    SELECT 
     Storage.item_id
    FROM 
     Storage
    WHERE 
      Storage.item_id = Item.id     
 )

参见演示 https://www.db-fiddle.com/f/phKyPgvGJpZ5x1Lj6sisCd/0

» Raymond Nijland 创建的更多回复
关于   移动版
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号