Conditional Query Execution

Execute queries only when parameter are not empty:

SELECT * FROM t_interlinkage 
WHERE IF('翁二翁' != '', name LIKE '%翁二翁%', 1=1);

Enhanced fuzzy search with concatenation:

SELECT * FROM t_interlinkage 
WHERE IF('翁二翁' != '', name LIKE CONCAT('%', '翁二翁', '%'), 1=1);

Comma-Separated Field Queries

Matching values within comma-separated fields:

-- Simple pattern matching
SELECT * FROM t_interlinkage WHERE role REGEXP '(^|,)0(,|$)';

-- Multiple value matching
SELECT * FROM user WHERE hobby_ids REGEXP '(^|,)(1|2)(,|$)';

JPA regex-based query with pagination:

@Query(value = "SELECT * FROM t_member WHERE guidance_type REGEXP '(^|,)('+?1+')(,|$)' AND name LIKE ('%'+?4+'%') LIMIT ?2,?3", nativeQuery = true)
List<TMember> findByGuidanceTypeRegx(String guidanceType, int pageNumber, int pageSize, String name);

Optmiized approach using FIND_IN_SET:

<if test="softwareIndustry != null and softwareIndustry != ''">
    AND
    <foreach collection="softwareIndustry.split(',')" index="index" item="item" open="(" separator="OR" close=")">
        FIND_IN_SET(#{item}, software_industry)
    </foreach>
</if>

JPA Soft Delete Implementation

Entity configuration for logical deletion:

@SQLDelete(sql = "UPDATE t_activation_code SET del_flag = 2 WHERE id = ?")
@Where(clause = "del_flag = 0")

@Column(name = "`del_flag`", columnDefinition = "char(1) DEFAULT '0'", insertable = false)
@ApiModelProperty(value = "Deletion status: 0=active, 2=deleted")
private String delFlag;

Ranking Based on Field Values

Single table ranking implementation:

SELECT i1.*,
       (SELECT COUNT(i2.id) 
        FROM welfare_donations_public i2 
        WHERE i2.donation_amount >= i1.donation_amount AND i2.del_flag = 0) AS rank
FROM welfare_donations_public i1
WHERE i1.del_flag = 0
ORDER BY i1.donation_amount DESC
LIMIT 10;

Date-Based Status Classification

Time-based activity status determination:

SELECT id, application_start_time, application_end_time, NOW(),
       CASE
           WHEN application_start_time > NOW() THEN 'B'
           WHEN application_start_time <= NOW() AND application_end_time >= NOW() THEN 'A'
           WHEN application_end_time < NOW() THEN 'C'
           ELSE 'B'
       END AS activityStatus
FROM activity
ORDER BY activityStatus ASC;

Pagination with LEFT JOIN Optimization

Subquery approach to eliminate duplicates:

SELECT u.*,
       (SELECT ua.num 
        FROM b ua 
        WHERE ua.user_id = u.id AND ua.del_flag = 0 AND ua.status = 1) AS num
FROM a u
WHERE u.del_flag = 0;

Multi-Table Deletion with Aliases

Specifying target table for deletion operations:

DELETE rd FROM table1 rd 
LEFT JOIN table2 rp ON rp.id = rd.pay_bill_id
LEFT JOIN table3 cn ON cn.id = rp.contract_id
WHERE cn.contract_status_code IN (500701, 500704) 
  AND rp.start_date >= '2024-07-01' 
  AND rp.end_date <= '2024-08-31';

Removing Line Breaks and Carriage Returns

Clean text data by removing special characters:

UPDATE your_table
SET your_column = REPLACE(REPLACE(your_column, CHAR(10), ''), CHAR(13), '');

Duplicate Detection Query

Identify and count duplicate records:

SELECT column_name, COUNT(*) as count
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

One-to-Many Latest Record Retrieval

Retrieve most recent related record:

SELECT u.id
FROM USERS u
LEFT JOIN USER_AUTH ua ON ua.id = (
    SELECT b.id 
    FROM USER_AUTH b 
    WHERE b.userId = u.id 
    ORDER BY b.id 
    LIMIT 1
);

-- Alternative approach
SELECT *
FROM USERS u
LEFT JOIN (
    SELECT MAX(id) AS id, userId 
    FROM USER_AUTH 
    GROUP BY userId
) AS ua ON ua.userId = u.id;

Sequential Numbering Logic

Implement custom sequence numbering:

int sequence = 0;
for (MyWorkDispatchSearchResVo resVo : resVoList) {
    if (vo.getPage() > 1) {
        int startNum = Math.max(vo.getLimit() * (vo.getPage() - 1), 0);
        sequence = (sequence < startNum + 1) ? sequence + startNum + 1 : sequence + 1;
    } else {
        sequence++;
    }
    resVo.setSequence(sequence);
}