Madhur Bhaiya

您需要将分隔符定义为除 ; (如: $$ )最后,将分隔符重新定义为 ; :

DELIMITER $$
CREATE FUNCTION IsRoomAvailable(rid INT,fymd DATE,tymd DATE,bid INT)
  RETURNS BOOL
  READS SQL DATA
BEGIN
  DECLARE bids INT;
  SELECT COUNT(*) INTO bids
    FROM bookings AS b
   WHERE ((b.bstart<tymd AND b.bend>=fymd) OR (b.bstart<=fymd AND b.bend>tymd))
     AND b.rid=rid
     AND b.bid!=bid;
  RETURN IF(bids>0,FALSE,TRUE);
END $$
DELIMITER ;

附笔. 我做了一个比较 5.7 Docs 和 8.0 Docs 对于MySQL中的用户定义函数。似乎什么都没变。

为此,首先需要在 Derived Table . 然后,使用子查询结果设置序列号(对于随机排序的行):

SET @a:= 0;
SELECT @a:=@a+1 as sequence_no,
       dt.captcha_id, 
       dt.captcha_name 
FROM (
       SELECT captcha_id, captcha_name 
       FROM captcha 
       ORDER BY RAND()
     ) AS dt

假设有一个主键列(假设 id )在您的表中,它可用于定义 Station 。 记得 这些数据以无序的方式存储,如果不定义特定的顺序和/或主键,我们就不能真正定义“last known”值。

Coalesce() 函数将用于处理 null 价值观 车站 在特定的一排。那么,我们可以用 Correlated Subquery 以确定“最后已知”的值。

SELECT 
  t1.Closed, 
  t1.Open, 
  t1.Plan, 
  COALESCE(t1.Station, 
           (SELECT t2.Station 
            FROM Orders AS t2 
            WHERE t2.id < t1.id 
              AND t2.Station IS NOT NULL 
            ORDER BY t2.id DESC 
            LIMIT 1)) AS Station 
FROM Orders AS t1

结果

| Closed     | Open | Plan       | Station |
| ---------- | ---- | ---------- | ------- |
| 2018-10-23 |      |            | A       |
| 2018-10-22 |      |            | A       |
| 2018-10-22 |      |            | B       |
| 2018-10-22 |      |            | B       |
|            |      | 2018-10-23 | C       |
|            |      | 2018-10-22 | C       |
|            |      | 2018-10-22 | C       |

View on DB Fiddle

你不需要使用 Group By 在查询中,当您获取在 storage 表。你的 Group BY 也不是有效的ANSI SQL,因为在 Select 条款。

SELECT item.id, item.name, storage.stuff 
FROM item 
LEFT JOIN storage ON storage.item_id=item.id 
WHERE storage.stuff IS NULL 

作为第一种方法(如果时间允许),您应该考虑 normalizing 你的桌子,就像“草莓”里说的 answer

然而,第二种方法允许任意数量的列(尽管由于字符串操作和冒泡排序而效率低下),利用 User Defined Functions .

我们基本上需要创建一个函数,它可以对逗号分隔字符串中的值进行排序。我找到了一个工作函数,可以进行排序。从复制代码 here :

-- sort comma separated substrings with unoptimized bubble sort
DROP FUNCTION IF EXISTS sortString;
DELIMITER |
CREATE FUNCTION sortString(inString TEXT) RETURNS TEXT
BEGIN
  DECLARE delim CHAR(1) DEFAULT ','; -- delimiter 
  DECLARE strings INT DEFAULT 0;     -- number of substrings
  DECLARE forward INT DEFAULT 1;     -- index for traverse forward thru substrings
  DECLARE backward INT;   -- index for traverse backward thru substrings, position in calc. substrings
  DECLARE remain TEXT;               -- work area for calc. no of substrings
-- swap areas TEXT for string compare, INT for numeric compare
  DECLARE swap1 TEXT;                 -- left substring to swap
  DECLARE swap2 TEXT;                 -- right substring to swap
  SET remain = inString;
  SET backward = LOCATE(delim, remain);
  WHILE backward != 0 DO
    SET strings = strings + 1;
    SET backward = LOCATE(delim, remain);
    SET remain = SUBSTRING(remain, backward+1);
  END WHILE;
  IF strings < 2 THEN RETURN inString; END IF;
  REPEAT
    SET backward = strings;
    REPEAT
      SET swap1 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward-1),delim,-1);
      SET swap2 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward),delim,-1);
      IF  swap1 > swap2 THEN
        SET inString = TRIM(BOTH delim FROM CONCAT_WS(delim
        ,SUBSTRING_INDEX(inString,delim,backward-2)
        ,swap2,swap1
        ,SUBSTRING_INDEX(inString,delim,(backward-strings))));
      END IF;
      SET backward = backward - 1;
    UNTIL backward < 2 END REPEAT;
    SET forward = forward +1;
  UNTIL forward + 1 > strings
  END REPEAT;
RETURN inString;
END |
DELIMITER ;

您将需要在mysql服务器上运行此代码,以便此函数在查询中可用,就像本地内置的mysql函数一样。现在,查询部分变得简单了。你要做的就是 Concat_ws() 所有使用逗号的数字列。然后,申请 sortString() 连接字符串上的函数。最后,在 Group By 子句,以获得所需的结果。

尝试:

SELECT sortString(CONCAT_WS(',', n1, n2, n3)) AS n_sequence -- add more columns here
       COUNT(id) AS total 
FROM your_table 
GROUP BY n_sequence 
ORDER BY total DESC 

现在我建议您可以使用应用程序代码更改逗号分隔的 n_sequence 返回表格列显示。

• 你可以用 Replace() 要替换的函数 SN 中包含空字符串的子字符串 SerialNumber 列。
• 那么,我们可以 Cast() 将字符串修改为无符号整数值。
• 最终,使用 Where 根据需要匹配范围的条件。

您可以尝试以下操作:

SELECT * 
FROM Devices 
WHERE CAST(REPLACE(SerialNumber, 'SN', '') AS UNSIGNED) >= 15000 AND 
      CAST(REPLACE(SerialNumber, 'SN', '') AS UNSIGNED) <= 20000 AND 
      SerialNumber LIKE 'SN%'

• 我们首先使用 Session variables .
• 然后,在 Derived table ,并找出总和超过40的“屏障”的日期。当新的滚动和大于或等于40时达到屏障。此时,我们将屏障值设置为1,前面的行设置为0,后面的行设置为2或更多。
• date 是MySQL中的关键字。您应该避免使用它作为表名;否则请使用反勾号。

try(适用于所有mysql版本):

SELECT dt.id, 
       dt.`date`, 
       dt.quantity
FROM 
(
  SELECT 
    f.id, 
    f.`date`, 
    f.quantity, 
    @barrier := CASE
                  WHEN 
                       (@tot_qty := @tot_qty + f.quantity) >= 40
                  THEN (@barrier + 1)
                  ELSE 0
                END AS barrier 
  FROM 
    Fruit AS f 
  CROSS JOIN (SELECT @tot_qty := 0, 
                     @barrier := 0) AS user_init
  ORDER BY f.`date`
) AS dt 
WHERE dt.barrier <= 1

DB Fiddle DEMO

不能使用聚合函数,如 Max() 里面 Where 条款。你可以简单地修改 where 条件仅包括2017年的日期,然后确定 小精灵 分组后的日期。

SELECT MAX(hs.dateFin), hs.codeAdherent, hs.codeArticle 
FROM hs 
WHERE hs.codeFamilleArticle IN ('CNI', 'COT', 'ABO', 'ABOW',
                                'CNIW', 'O&T', 'EPH', 'TAX') 
AND hs.codeAdherent != 0 
WHERE hs.dateFin BETWEEN '2017-01-01' 
                     AND '2017-12-31'
GROUP BY hs.codeAdherent, hs.codeArticle 

在mysql 8.0中,可以通过 Routines 表 INFORMATION_SCHEMA . 以下几点值得注意:

例程表提供有关存储的例程(存储的 过程和存储函数)。例程表不包括 内置SQL函数或用户定义函数(UDF)。

现在,这里需要使用例程表中的以下两列:

ROUTINE_SCHEMA -该例程所属的架构(数据库)的名称。

ROUTINE_TYPE -存储过程的过程,存储函数的函数。

关于进一步调查利用 SHOW CREATE TABLE information_schema.routines ,发现是 临时表

因此,为了删除 test 数据库,我们可以使用以下查询:

DELETE FROM information_schema.routines 
WHERE routine_type = 'PROCEDURE' AND 
      routine_schema = 'test'

• 首先将JSON转换为数组usng json_decode 第二个参数设置为的函数 true 。然后,可以循环遍历数组。

执行以下操作:

$rows = json_decode($rows, true);

• 最重要的是,您的查询是 正常开放 到 SQL injection . 请使用 Prepared statements .

首先,我认为您不必要地使用了子选择查询( Derived Table )获取当前结果。由于您没有对子查询结果进行任何进一步的筛选,因此您可以取消子查询,仍然可以更有效地获得相同的结果:

SELECT 
 d.dormid, 
 d.student_capacity, 
 COUNT(d.dormid) AS num 
FROM Dorm AS d 
LEFT JOIN Has_amenity AS h 
 ON h.dormid = d.dormid 
GROUP BY d.dormid, d.student_capacity 

现在,你基本上需要 Dense_Rank() 功能(排名1的宿舍设施最多)。 Window functions 仅在 MySQL versions 8.0.2 and onwards .

在版本中 mysql<8.0.2版 我们可以利用 User-defined variables ,计算相同的值。这种方法的基本要点是在派生表中按要求的顺序获取结果集。在这种情况下,我们关注的是排名最高的宿舍;因此我们得到的结果集按 num .

现在,我们使用两个会话变量来存储前一行的值,以便确定当前行的值,在 Select 条款。我们基本上存储前一行 rank 和 号码 价值观;以及 if 当前行的 号码 值与前一行不同,我们增加 等级 .

最后,再次将这个结果用作派生表,我们只需要考虑那些 rank = 1 (使用) Where 条款。)

尝试以下查询(适用于所有MySQL版本):

SELECT dt2.*
FROM 
(
  SELECT 
    dt.dormid, 
    dt.student_capacity, 
    @drank := IF(@n <> dt.num, @drank + 1, @drank) AS rank, 
    @n := dt.num AS num 
  FROM   
  (
    SELECT 
     d.dormid, 
     d.student_capacity, 
     COUNT(d.dormid) AS num 
    FROM Dorm AS d 
    LEFT JOIN Has_amenity AS h 
     ON h.dormid = d.dormid 
    GROUP BY d.dormid, d.student_capacity 
    ORDER BY num DESC 
  ) AS dt 
  CROSS JOIN (SELECT @drank := 0, 
                     @n := 0) AS user_init_vars 
) AS dt2 
WHERE dt2.rank = 1

我在PHP代码中做了类似的过滤。但是上下文是不同的;我们使用的是主从复制体系结构。为了将查询定向到相关服务器(将查询读取到从属服务器,并将查询写入主服务器),将编写一个自定义函数来标识 类型 查询。

值得注意的一点是,事务/锁定/解锁操作中的查询始终被视为写查询。

另外,对于 Set ,只有 SET AUTOCOMMIT 和 SET TRANSACTION 是写命令。

请在下面找到我们正在使用的实际代码的主要色调版本:

/*
* All the WRITE operation commands
*/
$write_commands = array(
    'create', 
    'alter', 
    'drop', 
    'truncate',
    'comment', 
    'rename', 
    'insert', 
    'update',
    'delete', 
    'merge', 
    'call', 
    'lock', 
    'unlock',
    'start', 
    'commit', 
    'rollback', 
    'savepoint',
    'set', 
    'replace' 
);


/*
* method to determine whether Read or Write
* @param $sql String (SQL query string)
* @return: void
*/
function determineReadOrWrite(string $sql): void {

    $dml_query = false;

    $words = str_word_count(strtolower(trim($sql)), 1);
    $first_word = isset($words[0]) ? $words[0] : '';
    $second_word = isset($words[1]) ? $words[1] : '';

    if (in_array($first_word, $this->write_commands)) {
        /* if it is not "set" then we set to master link */
        if ($first_word !== 'set'
        || ($first_word === 'set' && $second_word === 'autocommit')
        || ($first_word === 'set' && $second_word === 'transaction')
        ) {
            $dml_query = true;

            /* If we Lock tables or Begin a Transaction, we should run on Write servers only */
            /* till we Commit/Rollback or Unlock Tables */
            if(($first_word === 'start' && $second_word === 'transaction') 
            || $first_word === 'lock'){

                /* Set whether the current query is starting a Transaction / Lock etc */
                $this->wait_for_commit_rollback = true;
            }

            /* We are doing Commit/Rollback or Unlock Tables */
            if ($first_word === 'commit' 
            || $first_word === 'rollback' 
            || $first_word === 'unlock') {
                $this->wait_for_commit_rollback = false;
            }
        }
    }

    /* It's a insert/update/delete/etc query - to be run on Write Db only */
    if ($dml_query || $this->wait_for_commit_rollback) { 
        $this->setActiveConnectionToWrite(true);            

    } else {
        $this->setActiveConnectionToRead();
    }

}

• 得到 Maximum 价值 LocationSyncDateTime 对于一个 User_Name 在一个 Derived Table t3 .
• 将主表联接到此派生表 用户名 最大值为 位置同步日期时间 .

尝试以下操作:

SELECT u.Full_Name, 
       u.Mobile_Number, 
       l.Latitude, 
       l.Longitude, 
       l.LocationSyncDateTime 
FROM user AS u 
JOIN location AS l ON l.User_Name = u.User_Name 
JOIN (SELECT l2.User_Name, 
             MAX(l2.LocationSyncDateTime) AS LocationSyncDateTime 
      FROM location AS l2 
      GROUP BY l2.User_Name
     ) AS t3 ON t3.User_Name = l.User_Name 
                AND t3.LocationSyncDateTime = l.LocationSyncDateTime

交换 LIKE :

select nombre_busqueda, 
       soundex(nombre_busqueda), 
       soundex('derticia'), 
       concat('%', (soundex(nombre_busqueda)) , '%')  
from jugador j 
WHERE soundex(nombre_busqueda) like concat('%', soundex('derticia') , '%')

在MySQL中,可以使用 JOIN 在一 Derived Table :

SELECT empno,ename,deptno
FROM emp AS t1 
JOIN (SELECT t2.deptno 
      FROM emp AS t2 
      GROUP BY t2.deptno 
      ORDER BY COUNT(*) DESC 
      LIMIT 1,1
     ) AS t3 ON t3.deptno = t1.deptno