set @row_number = 0;
SELECT 
    *
FROM
    (SELECT 
        (@row_number:=@row_number + 1) AS num,
        id,
        tbl_user_id,
        title,
        description,
        length lengths,
        create_date,
        file_size,
        thumbnails,
        videos.itsOK,
        viewed
    FROM
        tbl_videos videos
    WHERE
        videos.tbl_user_id = 23
            AND videos.tbl_category_id = 265
        ORDER BY videos.create_date DESC
) AS paginateTbl
WHERE
    paginateTbl.num > 0
        && paginateTbl.num <= 9

mysql结果：

mariadb 结果：

内部查询对它们都有效，但主要查询仅在 mariadb 中有效！mysql不工作是怎么回事？

使用的版本是 mysql:5.5.44-0ubuntu0.14.04.1和 mariadb 10.0.13-MariaDB-log。

语句是相同的CREATE TABLE（除了AUTO_INCREMENT, 行数）：

MySQL结果：

SHOW CREATE TABLE tbl_videos;

CREATE TABLE `tbl_videos` (
    `id` INT (20) NOT NULL AUTO_INCREMENT
    ,`title` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`description` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tags` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`video_quality` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link1` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link2` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link3` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed` INT (11) NOT NULL
    ,`viewed_duration` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed_traffic` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`embed_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`sharing_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`replace_times` INT (11) NOT NULL
    ,`actual_link` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tbl_user_id` INT (11) NOT NULL
    ,`tbl_category_id` INT (11) NOT NULL
    ,`tbl_player_id` INT (11) NOT NULL
    ,`itsOK` TINYINT (2) NOT NULL
    ,`length` INT (20) NOT NULL
    ,`create_date` INT (11) NOT NULL
    ,`modified_date` INT (11) NOT NULL
    ,`thumbnails` TEXT COLLATE utf8_persian_ci
    ,`serverId` VARCHAR(32) COLLATE utf8_persian_ci NOT NULL
    ,`sizes` VARCHAR(100) COLLATE utf8_persian_ci DEFAULT NULL
    ,`our_server_link` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`like` INT (11) NOT NULL DEFAULT '0'
    ,`file_size` FLOAT DEFAULT NULL
    ,`islogo` TEXT COLLATE utf8_persian_ci
    ,`uuid` VARCHAR(64) COLLATE utf8_persian_ci DEFAULT NULL
    ,`output_type` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_file` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_setting` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`soft_hard` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`soft_hard_logo` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`vastTag` TEXT COLLATE utf8_persian_ci
    ,`extra_cat_id` INT (11) NOT NULL DEFAULT '0'
    ,`all_terafic` BIGINT (20) NOT NULL DEFAULT '0'
    ,PRIMARY KEY (`id`)
    ,KEY `tbl_user_id`(`tbl_user_id`)
    ,KEY `tbl_category_id`(`tbl_category_id`)
    ,CONSTRAINT `tbl_videos_ibfk_1` FOREIGN KEY (`tbl_user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ,CONSTRAINT `tbl_videos_ibfk_2` FOREIGN KEY (`tbl_category_id`) REFERENCES `tbl_categories`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = InnoDB AUTO_INCREMENT = 4622 DEFAULT CHARSET = utf8 COLLATE = utf8_persian_ci

MariaDB 结果：

SHOW CREATE TABLE tbl_videos;

CREATE TABLE `tbl_videos` (
    `id` INT (20) NOT NULL AUTO_INCREMENT
    ,`title` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`description` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tags` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`video_quality` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link1` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link2` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`dl_link3` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed` INT (11) NOT NULL
    ,`viewed_duration` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`viewed_traffic` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`embed_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`sharing_code` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`replace_times` INT (11) NOT NULL
    ,`actual_link` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`tbl_user_id` INT (11) NOT NULL
    ,`tbl_category_id` INT (11) NOT NULL
    ,`tbl_player_id` INT (11) NOT NULL
    ,`itsOK` TINYINT (2) NOT NULL
    ,`length` INT (20) NOT NULL
    ,`create_date` INT (11) NOT NULL
    ,`modified_date` INT (11) NOT NULL
    ,`thumbnails` TEXT COLLATE utf8_persian_ci
    ,`serverId` VARCHAR(32) COLLATE utf8_persian_ci NOT NULL
    ,`sizes` VARCHAR(100) COLLATE utf8_persian_ci DEFAULT NULL
    ,`our_server_link` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`like` INT (11) NOT NULL DEFAULT '0'
    ,`file_size` FLOAT DEFAULT NULL
    ,`islogo` TEXT COLLATE utf8_persian_ci
    ,`uuid` VARCHAR(64) COLLATE utf8_persian_ci DEFAULT NULL
    ,`output_type` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_file` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`video_setting` TEXT COLLATE utf8_persian_ci NOT NULL
    ,`soft_hard` VARCHAR(255) COLLATE utf8_persian_ci DEFAULT NULL
    ,`soft_hard_logo` VARCHAR(255) COLLATE utf8_persian_ci NOT NULL
    ,`vastTag` TEXT COLLATE utf8_persian_ci
    ,`extra_cat_id` INT (11) NOT NULL DEFAULT '0'
    ,`all_terafic` BIGINT (20) NOT NULL DEFAULT '0'
    ,PRIMARY KEY (`id`)
    ,KEY `tbl_user_id`(`tbl_user_id`)
    ,KEY `tbl_category_id`(`tbl_category_id`)
    ,CONSTRAINT `tbl_videos_ibfk_1` FOREIGN KEY (`tbl_user_id`) REFERENCES `tbl_users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ,CONSTRAINT `tbl_videos_ibfk_2` FOREIGN KEY (`tbl_category_id`) REFERENCES `tbl_categories`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = InnoDB AUTO_INCREMENT = 9387 DEFAULT CHARSET = utf8 COLLATE = utf8_persian_ci

mysql结果：

EXPLAIN SELECT * from FROM ...
id  select_type table       type    possible_keys   key key_len ref rows    Extra
1   PRIMARY     <derived2>  ALL NULL    NULL    NULL    NULL    14  Using where
2   DERIVED     videos      index_merge tbl_user_id,tbl_category_id tbl_category_id,tbl_user_id 4,4 NULL    1   Using intersect(tbl_category_id,tbl_user_id); Using where; Using filesort

mariadb 结果：

EXPLAIN SELECT * from tbl_videos
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    2   Using where
2   DERIVED videos  index_merge tbl_user_id,tbl_category_id tbl_category_id,tbl_user_id 4,4 NULL    1   Using intersect(tbl_category_id,tbl_user_id); Using where; Using filesort

是否可以配置 SQL Server Management Studio (SSMS)。这样当我关闭未保存的查询窗口时不会出现上面显示的对话框？

select case 'NULL' when 'NULL' then 'f' end as out

输出：f

但

select case (select  (SELECT LEFT(l.list,LEN(l.list)-1) 
    FROM
    (SELECT  o.Reciting_Path + '|' + o.Declaimer +',' AS [text()]
    FROM    Poetry_Reciting  AS o
    where   o.Poetry_Id=6764
    FOR XML PATH(''))l(list)) )  when  'NULL' then 'N' end as dd

应该只返回NULL并且 NULL 是正确的，但 case 语句不起作用。

我想要输出中的“N”

如何查找长度小于或等于的单词...

  declare @inp nvarchar(max),@data nvarchar(max)
  set @inp='You can dance, you can jive, having .... jove... jve, ...' 
  set @data = 'jeve'
  select @inp as results where @inp like '%' + @data +'%'

@inp 不返回任何数据，但我想显示：

results 
=====
jive
jove
jve

如何patindex在表格或变量中找到所有位置？

declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek  '
  + 'dokhtare khoshkel be disco raft va ali baraye'
  + ' 1 saat anja bud va sepas... ali...'
select patindex('%ali%',@name) as pos 

这会返回1，但我想要所有结果，例如：

pos
===
  1
 74
113

declare @xml xml,
declare @num int
set @num = 1
set @xml = '
<row>
    <Id>1</Id>
    <name>reza</name>
</row>
<row>
    <Id>2</Id>
    <name>Masoud</name>
</row>
'

如何选择 ID = @num ??

如何防止每次执行重复插入？

insert into  IQ (XMLDT)values('<row>sdsdf</row>')

输入：

Declare @OUT xml
select @OUT = N'
<row>
  <kind>MainCat</kind>
  <sortid>1</sortid>
  <kind_id>1</kind_id>
  <PPoet>حافظ</PPoet>
  <MTitle>حافظ</MTitle>
  <Row>1</Row>
</row>'

select T.X.value('@Kind', 'nvarchar(50)') as kind,
T.X.value('@sortid', 'int') as sortid
from @out.nodes('/row') as T(X)

this select return NULL!

输入：我有一个包含（任意数量）用户提供的字符串的表：

╔═══════╗
║ Value ║
╠═══════╣
║ To    ║
║ An    ║
╚═══════╝

以及用户详细信息表：

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
║     32 ║ Dina     ║
║      2 ║ Mark     ║
╚════════╩══════════╝

我需要一个查询来返回用户表中的所有记录，其中输入表中UserName任何记录的部分字符串匹配。Value这种情况下的预期输出是：

╔════════╦══════════╗
║ UserID ║ UserName ║
╠════════╬══════════╣
║     12 ║ Tom      ║
║    132 ║ Ann      ║
╚════════╩══════════╝

我需要一个可以在（或作为）函数中使用并检索 n 值的所有组合的查询。我需要所有长度为 k 的组合，其中 k = 1..n。

扩展样本输入和结果，因此输入有 3 个值而不是 2 个 - 但是，输入值的数量可能在 1 到 n 之间变化。

示例：输入：多行中一列中的值的表

Value  (nvarchar(500))
------
Ann
John
Mark

输出#1：一列中连接值的表

    Ann
    John
    Mark
    Ann,John
    John,Mark
    Ann,Mark
    Ann,John,Mark