search

有这样一个疑问,如果是一系列比较复杂的操作,跨表、累加之类的,简单的 sql 可能需要执行两个以上才能完成。

用编程语言多次执行,结果集里面折腾有效率呢;还是想尽办法写个高深的 sql,一次解决问题呢?

俺 sql 很水,编程语言也只是中下游水平,又有一颗追求优雅、高效之心。

举个例子:

有个临时表,存储 pid, tid, duration,通过一次 select,将 (pid, tid) 作为唯一组合,统计其出现次数(count),和 duration 累加总和。
然后将 pid, tid, count, duration 存入另外一个表,还要保证另外一个表的 (pid, tid) 是唯一组合。

插入部分的唯一性已经写出了稍微复杂的 sql,现在在考虑是将第一部分的结果集遍历再分别插入,还是再考虑一个更复杂的 sql。

ps: 第二部分插入的题目在:http://segmentfault.com/q/1010000000723454
所用的 sql 为:

<code>alter table stat add unique index pid_and_tid (pid, tid);

insert into stat (pid, tid, count, duration)
    values (1, 1, 1, 1)
    on duplicate key update
    count = count + 1, duration = duration + 1
</code>

回复内容:

有这样一个疑问,如果是一系列比较复杂的操作,跨表、累加之类的,简单的 sql 可能需要执行两个以上才能完成。

用编程语言多次执行,结果集里面折腾有效率呢;还是想尽办法写个高深的 sql,一次解决问题呢?

俺 sql 很水,编程语言也只是中下游水平,又有一颗追求优雅、高效之心。

举个例子:

有个临时表,存储 pid, tid, duration,通过一次 select,将 (pid, tid) 作为唯一组合,统计其出现次数(count),和 duration 累加总和。
然后将 pid, tid, count, duration 存入另外一个表,还要保证另外一个表的 (pid, tid) 是唯一组合。

插入部分的唯一性已经写出了稍微复杂的 sql,现在在考虑是将第一部分的结果集遍历再分别插入,还是再考虑一个更复杂的 sql。

ps: 第二部分插入的题目在:http://segmentfault.com/q/1010000000723454
所用的 sql 为:

<code>alter table stat add unique index pid_and_tid (pid, tid);

insert into stat (pid, tid, count, duration)
    values (1, 1, 1, 1)
    on duplicate key update
    count = count + 1, duration = duration + 1
</code>

几乎任何时候降低数据库的 计算复杂度 都是好的。

内存可重复利用,容易扩展,在大多数场景下都可以利用内存来换取数据库性能,即:从数据库中简单地取出数据,再在内存中处理成想要的数据。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you create and use an interface in PHP?How do you create and use an interface in PHP?Apr 30, 2025 pm 03:40 PM

The article explains how to create, implement, and use interfaces in PHP, focusing on their benefits for code organization and maintainability.

What is the difference between crypt() and password_hash()?What is the difference between crypt() and password_hash()?Apr 30, 2025 pm 03:39 PM

The article discusses the differences between crypt() and password_hash() in PHP for password hashing, focusing on their implementation, security, and suitability for modern web applications.

How can you prevent Cross-Site Scripting (XSS) in PHP?How can you prevent Cross-Site Scripting (XSS) in PHP?Apr 30, 2025 pm 03:38 PM

Article discusses preventing Cross-Site Scripting (XSS) in PHP through input validation, output encoding, and using tools like OWASP ESAPI and HTML Purifier.

What is autoloading in PHP?What is autoloading in PHP?Apr 30, 2025 pm 03:37 PM

Autoloading in PHP automatically loads class files when needed, improving performance by reducing memory use and enhancing code organization. Best practices include using PSR-4 and organizing code effectively.

What are PHP streams?What are PHP streams?Apr 30, 2025 pm 03:36 PM

PHP streams unify handling of resources like files, network sockets, and compression formats via a consistent API, abstracting complexity and enhancing code flexibility and efficiency.

What is the maximum size of a file that can be uploaded using PHP ?What is the maximum size of a file that can be uploaded using PHP ?Apr 30, 2025 pm 03:35 PM

The article discusses managing file upload sizes in PHP, focusing on the default limit of 2MB and how to increase it by modifying php.ini settings.

What is Nullable types in PHP ?What is Nullable types in PHP ?Apr 30, 2025 pm 03:34 PM

The article discusses nullable types in PHP, introduced in PHP 7.1, allowing variables or parameters to be either a specified type or null. It highlights benefits like improved readability, type safety, and explicit intent, and explains how to declar

What is the difference between the unset() and unlink() functions ?What is the difference between the unset() and unlink() functions ?Apr 30, 2025 pm 03:33 PM

The article discusses the differences between unset() and unlink() functions in programming, focusing on their purposes and use cases. Unset() removes variables from memory, while unlink() deletes files from the filesystem. Both are crucial for effec

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function